Generate File name within worksheet

[Chuck]

I am trying to get data from several workbooks into a worksheet.

Each item has its own workbook. I need to run a series of calculations for
each item and then reference that data into a worksheet in a different
workbook.

Lets say the source workbooks are named HP-1, HP-2, HP-3, ...

In my active worksheet, I want to reference data from the same location in
each of the source workbooks. As an example,

The source file name is HP-3.xls. The sourcefile worksheet name is
COOL_STR.
The value in C5 of my active worksheet is 3.

TO get the desired data I tried

=+'<path>["HP-"& Range("C5").Value &".xls"]COOL_STR'!$D$215

but it doesn't work.

I am trying to figure out the correct syntax to get Excel to find the source
file and return the value in Cell D215.

If I can find the correct syntax, I hope to be able to copy the formula down
a column and retrieve the values from the complete series of source files.

Can you help??

 

[Dave Peterson]

The function you'd want to use is called =indirect(). But the bad news is it
won't work with closed workbooks.

So if you have those other workbooks open, you can do it.

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

I am trying to get data from several workbooks into a worksheet.

Each item has its own workbook. I need to run a series of calculations for
each item and then reference that data into a worksheet in a different
workbook.

Lets say the source workbooks are named HP-1, HP-2, HP-3, ...

In my active worksheet, I want to reference data from the same location in
each of the source workbooks. As an example,

The source file name is HP-3.xls. The sourcefile worksheet name is
COOL_STR.
The value in C5 of my active worksheet is 3.

TO get the desired data I tried

=+'<path>["HP-"& Range("C5").Value &".xls"]COOL_STR'!$D$215

but it doesn't work.

I am trying to figure out the correct syntax to get Excel to find the source
file and return the value in Cell D215.

If I can find the correct syntax, I hope to be able to copy the formula down
a column and retrieve the values from the complete series of source files.

Can you help??

 

[Chuck]

Thanks Dave. So that I understand what is happening, my current cell
formula is:

=+'K:\path\[HP-1.xls]COOL_STR'!$D$217 which works great.

if I manually change the 1 to a 3, the formula immediately updates with the
data in HP-3 even though that file is closed.

Apparently adding the process of looking up a value for the HP-? must remove
its ability to update the data in a closed file?

Thanks

The function you'd want to use is called =indirect(). But the bad news is it
won't work with closed workbooks.

So if you have those other workbooks open, you can do it.

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

I am trying to get data from several workbooks into a worksheet.

Each item has its own workbook. I need to run a series of calculations for
each item and then reference that data into a worksheet in a different
workbook.

Lets say the source workbooks are named HP-1, HP-2, HP-3, ...

In my active worksheet, I want to reference data from the same location in
each of the source workbooks. As an example,

The source file name is HP-3.xls. The sourcefile worksheet name is
COOL_STR.
The value in C5 of my active worksheet is 3.

TO get the desired data I tried

=+'<path>["HP-"& Range("C5").Value &".xls"]COOL_STR'!$D$215

but it doesn't work.

I am trying to figure out the correct syntax to get Excel to find the source
file and return the value in Cell D215.

If I can find the correct syntax, I hope to be able to copy the formula down
a column and retrieve the values from the complete series of source files.

Can you help??