Value from another workbook based on calculated value?

[ohoh]

I've been doing excel for years now but am now stumped. How can
retrieve the contents of a cell (say, $A$10) from another (unopened
workbook based on the value of a cell in the current workbook.

Basically, I want to be able to enter a filename (such as "trial" o
001) as text or a number in a cell and then have a function return th
value of some cell (say, Sheet1!$A$1) that is contained in the filenam
just entered (i.e. the value in "[trial.xls]Sheet1!$A$1"). If I chang
"trial" to "test", the value returned would now be fro
"[test.xls]Sheet1!$A$1").

Is this possible? I'm guessing so and I've just been thinking about i
too long.

Thanks!
OhO

 

[Myrna Larson]

You can use the INDIRECT function to specify the workbook name (check it out
in Help), but for it to automatically update, I believe the named workbook
must be open. To work around that would require a VBA event macro.

 

[Dave Peterson]

And Harlan Grove already wrote that VBA UserDefinedFunction:
http://www.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've been doing excel for years now but am now stumped. How can I
retrieve the contents of a cell (say, $A$10) from another (unopened)
workbook based on the value of a cell in the current workbook.

Basically, I want to be able to enter a filename (such as "trial" or
001) as text or a number in a cell and then have a function return the
value of some cell (say, Sheet1!$A$1) that is contained in the filename
just entered (i.e. the value in "[trial.xls]Sheet1!$A$1"). If I change
"trial" to "test", the value returned would now be from
"[test.xls]Sheet1!$A$1").

Is this possible? I'm guessing so and I've just been thinking about it
too long.

Thanks!
OhOh

 

[Frank Kabel]

Hi
also see:
http://tinyurl.com/2c62u
(In your case INDIRECT.EXT should work)