Cell Reference to another excel document

[Guest]

Hi there,
I'm trying to create a cell reference to another cell in another excel
sheet, however the other excel sheet i am looking to update from is
determined by a value in the current sheet.

E.g.
A B
1 20.04.04 27.04.04
2 ? ?

I am looking to update the cells in Row 2 on my main sheet (above). The
values i am looking to bring in can be found in Row 50 on the excel document
named after the values in Row 1 e.g (the value for cell A2 can be found in
row 50 of the Excel document named "20.04.04"). Is there any way to have one
formula in row 2 which which get a value from the excel document named after
the value in Row A.

I'm not sure if this is possible but if it is any help would be much
appreciated.

thanks, Al

 

[Arvi Laanemets]

Hi

Without VBA you can get a range reference using INDIRECT function. But it
works only, until the source workbook is opened! When you close the workbook
with source data, the formula returns an error.

 

[Guest]

Thanks for the advice but unfortunately this would be unpractical.

Cheers, Al

 

[Stefan Hägglund [MSFT]]

Hi!

You can use INDIREKT, as Arvi answered, like the formula below.
Enter the external filenamne in A1
Enter this formulas in an empty cell =INDIRECT("["&A1&".xls]"&"Sheet1!A1")
If you close the external file you get a #REF!

Best regards

Stefan Hägglund
Microsoft

 

[Dave Peterson]

Harlan Grove wrote a userdefined function that can open another instance of
excel and retrieve the value from the closed workbook there.

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