refer to different workbook based on value in current workbook

  • Thread starter Thread starter LKG
  • Start date Start date
L

LKG

I'm working in Excel 2000 on Win98 SE. I have a lot of
files named by date, ie. 20040226.xls, 20040227.xls,
20040228.xls, etc. I want to bring various ranges of data
from all those files into 1 central file, after which I'll
convert the formulas to values, getting rid of the links.

The cells in ColA of my central workbook are date serials
displayed in mm/dd/yy format. I know that to get the data
I want, I can enter the name of each workbook manually.

A B
1 02/26/04 =[20040226.xls]Sheet1!B$20
2 02/27/04 =[20040227.xls]Sheet1!B$20
3 02/28/04 =[20040228.xls]Sheet1!B$20

But I need a more flexible way, using the value in ColA to
reference the name of the workbook. The Text
(CellReference, "yyyymmdd") worksheet function only
produces a string containing the formula, not the results
of the formula. The Indirect worksheet function requires
the other workbooks to be open. And Data->Consolidate
doesn't seem applicable because I don't want to perform
any operations (eg. sum, count, etc.) on the external
data, I just want to pull it in.

Please help. I'd prefer to do this using built-in
functions in the worksheet itself, but I'll create a VBA
sub/function if necessary.
 
Like you noted: If that other file is open, they you could use excel's
=indirect() function.

But Harlan Grove posted a function that retrieves the value from a separate
instance of excel:
http://google.com/[email protected]

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


I'm working in Excel 2000 on Win98 SE. I have a lot of
files named by date, ie. 20040226.xls, 20040227.xls,
20040228.xls, etc. I want to bring various ranges of data
from all those files into 1 central file, after which I'll
convert the formulas to values, getting rid of the links.

The cells in ColA of my central workbook are date serials
displayed in mm/dd/yy format. I know that to get the data
I want, I can enter the name of each workbook manually.

A B
1 02/26/04 =[20040226.xls]Sheet1!B$20
2 02/27/04 =[20040227.xls]Sheet1!B$20
3 02/28/04 =[20040228.xls]Sheet1!B$20

But I need a more flexible way, using the value in ColA to
reference the name of the workbook. The Text
(CellReference, "yyyymmdd") worksheet function only
produces a string containing the formula, not the results
of the formula. The Indirect worksheet function requires
the other workbooks to be open. And Data->Consolidate
doesn't seem applicable because I don't want to perform
any operations (eg. sum, count, etc.) on the external
data, I just want to pull it in.

Please help. I'd prefer to do this using built-in
functions in the worksheet itself, but I'll create a VBA
sub/function if necessary.
Click to expand...
 
Back
Top