Generate File name within worksheet

  • Thread starter Thread starter Chuck
  • Start date Start date
C

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??
 
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??
Click to expand...
 
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





Dave Peterson said:
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??
Click to expand...
Click to expand...
 
Dave Peterson said:
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]
Click to expand...
m
....

Picky: my function pull() creates a separate excel instance, but it doesn't
open the other workbook in the usual sense. Granted Excel must open (in the
OS sense) the other workbook file to fetch information from it, but it
doesn't load the entire workbook into memory.
 
Thanks Harlan and Dave

I tried the following, and am getting an #NAME?

=VLOOKUP(C14,pull("'"&K:\xxxxxxxx\xxxxxxx xxxxxxxxxx xxxxxxx x
xxxxxxx\xxxx\xxxx
xxxxxx\&"[HP-"&(B14)&".xls]COOL_STR"&"'!$C$215:$F$223"),2,0)

I replaced actual characters with x's in the path.
C14 is the reference row value in the lookup worksheet
B14 = 4

Can you see my error (besides the formula being very long)
 
Yep.

I had forgotten how it worked.



Harlan Grove said:
m
...

Picky: my function pull() creates a separate excel instance, but it doesn't
open the other workbook in the usual sense. Granted Excel must open (in the
OS sense) the other workbook file to fetch information from it, but it
doesn't load the entire workbook into memory.
Click to expand...
 
I can get a name error if I didn't put Harlan's =Pull() function in a general
module of the same workbook.

Where did you put your copy?

Another guess is a typo:

You want to surround the path with double quotes:

=VLOOKUP(C14,pull("'K:\xxxxxxxx\xxxxxxx\[HP-" & B14 &
".xls]COOL_STR'!$C$215:$F$223"),2,0)
All one cell.

(And instead of adding more double quotes, I removed a few.)


Thanks Harlan and Dave

I tried the following, and am getting an #NAME?

=VLOOKUP(C14,pull("'"&K:\xxxxxxxx\xxxxxxx xxxxxxxxxx xxxxxxx x
xxxxxxx\xxxx\xxxx
xxxxxx\&"[HP-"&(B14)&".xls]COOL_STR"&"'!$C$215:$F$223"),2,0)

I replaced actual characters with x's in the path.
C14 is the reference row value in the lookup worksheet
B14 = 4

Can you see my error (besides the formula being very long)

Harlan Grove said:
http://google.com/[email protected]
m
...

Picky: my function pull() creates a separate excel instance, but it doesn't
open the other workbook in the usual sense. Granted Excel must open (in the
OS sense) the other workbook file to fetch information from it, but it
doesn't load the entire workbook into memory.
Click to expand...
Click to expand...
 
Putting the pull() function in a general module solved the problem.

I modified the formula as follows:

=pull("'K:\xxxxxxxx\xxxxxxx xxxxxxxxxx xxxxxxx x xxxxxxx\xxxx\xxxx
xxxxxx\[HP-" &(B16) &".xls]COOL_STR'!$D$217")

It works very quickly and may be more efficient since I don't need to look
up an array.

Thanks for all of your help. I didn't think it would be quite this
complicated.

Happy Thanksgiving.

Chuck


Dave Peterson said:
I can get a name error if I didn't put Harlan's =Pull() function in a general
module of the same workbook.

Where did you put your copy?

Another guess is a typo:

You want to surround the path with double quotes:

=VLOOKUP(C14,pull("'K:\xxxxxxxx\xxxxxxx\[HP-" & B14 &
".xls]COOL_STR'!$C$215:$F$223"),2,0)
All one cell.

(And instead of adding more double quotes, I removed a few.)


Thanks Harlan and Dave

I tried the following, and am getting an #NAME?

=VLOOKUP(C14,pull("'"&K:\xxxxxxxx\xxxxxxx xxxxxxxxxx xxxxxxx x
xxxxxxx\xxxx\xxxx
xxxxxx\&"[HP-"&(B14)&".xls]COOL_STR"&"'!$C$215:$F$223"),2,0)

I replaced actual characters with x's in the path.
C14 is the reference row value in the lookup worksheet
B14 = 4

Can you see my error (besides the formula being very long)

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

Picky: my function pull() creates a separate excel instance, but it doesn't
open the other workbook in the usual sense. Granted Excel must open
Click to expand...
(in
the
OS sense) the other workbook file to fetch information from it, but it
doesn't load the entire workbook into memory.
Click to expand...
Click to expand...
Click to expand...
 
Harlan did very nice work.
Putting the pull() function in a general module solved the problem.

I modified the formula as follows:

=pull("'K:\xxxxxxxx\xxxxxxx xxxxxxxxxx xxxxxxx x xxxxxxx\xxxx\xxxx
xxxxxx\[HP-" &(B16) &".xls]COOL_STR'!$D$217")

It works very quickly and may be more efficient since I don't need to look
up an array.

Thanks for all of your help. I didn't think it would be quite this
complicated.

Happy Thanksgiving.

Chuck

Dave Peterson said:
I can get a name error if I didn't put Harlan's =Pull() function in a general
module of the same workbook.

Where did you put your copy?

Another guess is a typo:

You want to surround the path with double quotes:

=VLOOKUP(C14,pull("'K:\xxxxxxxx\xxxxxxx\[HP-" & B14 &
".xls]COOL_STR'!$C$215:$F$223"),2,0)
All one cell.

(And instead of adding more double quotes, I removed a few.)


Thanks Harlan and Dave

I tried the following, and am getting an #NAME?

=VLOOKUP(C14,pull("'"&K:\xxxxxxxx\xxxxxxx xxxxxxxxxx xxxxxxx x
xxxxxxx\xxxx\xxxx
xxxxxx\&"[HP-"&(B14)&".xls]COOL_STR"&"'!$C$215:$F$223"),2,0)

I replaced actual characters with x's in the path.
C14 is the reference row value in the lookup worksheet
B14 = 4

Can you see my error (besides the formula being very long)

...
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]
m
...

Picky: my function pull() creates a separate excel instance, but it
doesn't
open the other workbook in the usual sense. Granted Excel must open (in
the
OS sense) the other workbook file to fetch information from it, but it
doesn't load the entire workbook into memory.
Click to expand...
Click to expand...
Click to expand...
 
Sorry if i'm off the mark here.
If i understand you want to return values from many workbooks i
specific sheets and cells to one workbook (master sheet).

if so then won't ='C:\folder1\[workbook1.xls]sheet1'e5

in your taget cell

work?

tell me if I have completely misunderstood,
apologies in advance if so

bu
 
That'll work if the workbook name is typed into the formula. The original
poster wanted to have the workbook name in a different cell and use a formula to
essentially build that formula.

That way Chuck could just change the workbook name in that other cell and the
formula will evaluate correctly. The bad news is that =indirect() won't work
with a closed workbook.

That's where Harlan Grove's UDF came in very handy.

Sorry if i'm off the mark here.
If i understand you want to return values from many workbooks in
specific sheets and cells to one workbook (master sheet).

if so then won't ='C:\folder1\[workbook1.xls]sheet1'e5

in your taget cell

work?

tell me if I have completely misunderstood,
apologies in advance if so

bud
Click to expand...
 
Back
Top