Formula Referencing other cells

[ChrisJ]

Hi,

I am trying to remember how to do something that Im sure I used to do many years ago...

I am trying to have a formula be dependant on the text in another cell for example

Cell a1 = Redruth

Cell a2 = =+'C:\11.MPE Data\Sept\[MPE FY09 - "VALUE of A1" Sept.xls]Local Currency'!$B$8

Value of a2 should be =+'C:\11.MPE Data\Sept\[MPE FY09 - Redruth Sept.xls]Local Currency'!$B$8

I can't seem to be able to correctly reference A1 in the formula ..

Many thanks in advances

[Aflatoon]

You need to use INDIRECT to do that but in order for that to work, the other workbook has to be open - will that be an issue?

[ChrisJ]

Hi,
That will not be an issue... can you elaborate.
Many thanks
Chris

[Aflatoon]

You would need:
=INDIRECT("'C:\11.MPE Data\Sept\[MPE FY09 - " & A1 & " Sept.xls]Local Currency'!$B$8")

[ChrisJ]

Ahh..... I was wrong, I didn't want to have to have the C:\ .... file open at the same time,
Is there any alternative ?
Many thanks

[Zack Barresse]

INDIRECT.EXT() or PULL(), neither of which I'd consider efficient. INDIRECT.EXT() pretty much only works one time (then the file needs to be opened, at least in my experiences a couple of years ago with it, from the Morefunc.xll add-in by Laurent Longre), and the PULL() method by Harlan Grove basically uses code to open the workbook and grab the value, which isn't too efficient. Basically it's best to have the workbook open.