Hello-
I have been trying to get this to work, and not having
much luck. One problem is I am stuck in the INDIRECT
logic, and now I cannot figure another way to do this?
In the CLOSED workbook, I have a PIVOT TABLE of SALES DATA
Columns are Products
Rows are Daily Sales Figuers
A B C
1/1/3 1.1 1.2 1.3
1/2/3 2.1 2.2 2.3
1/3/3 3.1 3.2 3.3
Product A Sold 3.1$ on Jan 3, 2003.
In the OPEN workbook, I am trying to do a SUMMARY report
with:
Todays Sales
WeekToDate Sales
MonthToDate Sales
YearToDate Sales
I have all this working in INDIRECT, but found out AFTER
I did this that INDIRECT has to be in the same sheet. I
have to do this for 2 years of data, so I am trying to
keep the datasheet separate.
Once a date is entered, I calculate the START/END Dates
of each WTD, MTD, YTD totals. This ADDRESS got me a
reference for a STARTING POINT, and an END POINT.
I then could easily use INDIRECT:
SUM(INDIRECT(ADDRESS(START POINT)):INDIRECT(ADDRESS(END
POINT)), in which ALL these sums are calculated, allowing
the person to choose any date of the year.
==SUM(INDIRECT(ADDRESS($H$12,$B22,,,"[scorecard16.xls]SKU-
POS")):INDIRECT(ADDRESS($I$12,$B22,,,"[scorecard16.xls]
SKU-POS")))
I tried to move it to INDEX formulas.
SUM(INDEX(FIELD)) worked fine.
SUM(INDEX(START POINT):INDEX(END POINT)) behaves like
ADDRESS, in which it works when sheet is openned, and
returns a !REF error when the sheet is closed.
=SUM(INDEX('U:\Daily\LY-02\[scorecard16.xls]SKU-POS'!
A1:AZ400, $H$12,$B22):INDEX('U:\Daily\LY-02
\[scorecard16.xls]SKU-POS'!A1:AZ400, $I$12,$B22))
In working with the SQL.REQUEST, I had hoped to select
the SUM of numbers, working on the range. Since my DATA
is a PIVOT table, I am not able to use column names
reliably. I have started using relative column
names "F1, F2" etc. I can not get my WHERE clause to
return a value... it always returns a N/A error. I
cannot get the correct syntax for WHERE F3 = "1/3/3"
=SELECT F4 FROM AvailableSKUS WHERE to_char
(F3,"MM/DD/YY") = "03/02/02"
I have been stuck on this for a week.
HELP!? PLEASE
--Jason
-----Original Message-----
Tom, take a look at SQL.REQUEST, the function Microsoft supplies in its
XLODBC add-in. Not only can you use variable references to get data from
closed workbooks, you can also use it to update values in closed workbooks.
For illustrations on how to get data from closed workbooks see:
http://www.bygsoftware.com/examples/zipfiles/UsingSqlRequ est.zip
It's in the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm
This workbook demonstrates how to get data direct from an MS Access table,
or from an open or closed MS Excel workbook using the workbook function
SQL.REQUEST.
Recently updated to show the use of SQL.REQUEST in the same workbook.
The code is open and commented.
--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"
Tom Ogilvy said:
Only hard coded references work with references to closed workbooks.
Harlan Grove has posted a VBA solution that opens the
workbook in a
separate
instance of Excel and gets the value, but that would probably be too slow
and may have problems with cached copies and the latest version.
--
Regards,
Tom Ogilvy
Hello,
I use excel97
I want my cell to reference some cell on a sheet on the
internet. I do not know which cell this will be: its
column and row are calculated from user input.
I tried 3 excel functions (all 3 failed);
INDEX()
INDIRECT(ADDRESS())
OFFSET()
If i reference just a local workbook. all 3 work fine. BUt
when using internet references all fail.
For instance:
=OFFSET('
http://meta.fgov.be/excel/[nla13.xls]Blad1'!
J69;0;20)
or
=INDIRECT(ADDRESS(69;(YEAR(D15)-1998)*4+ROUNDUP(MONTH
(D15)/3;0)+2;4;1;"
http://meta.fgov.be/excel/ [nla13.xls]
Blad1"))
Another strange thing:
the help with excel97 file says a link should be written as
= [
http://meta.fgov.be/excel/nla13.xls]Blad1'!$J$69
but that didn't work; what did work was
= '
http://meta.fgov.be/excel/[nla13.xls]Blad1'!$J$69
I tried both ways of referencing with OFFSET, INDIRECT or
INDEX. And still no result.
Anybody any idea?
Thanks alot
Bart
.
