Cell Reference to another excel document

A

Al-Blakie

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
 
A

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.
 
S

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
 
Top