Can I make the formula (pathway) update when I change the value of A2?

[MPH88]

I am using the formula below to get a cell value from another file:

A3 ='/Users/MH/Base Rate Tables/[SampleBaseRateProposed118.xls]Sheet1'!$A1

If I input the name of the file I want to refer to in cell A2, can I make the formula (pathway) update when I change the value of A2?

Basically, I want this:

A2 = Find_Whatever_File_I_Input_In_Cell_A2

A3 ='/Users/MH /Base Rate Tables/[Find_Whatever_File_I_Input_In_Cell_A2]Sheet1'!$A1

 

[JLGWhiz]

I thihk you would have to use this syntax
A3 ="/Users/MH /Base Rate Tables/["&A2&"]Sheet1!$A1"

I don't know what you are using it for, but it probably needs the drive letter included, eg. "C:\..."

 

[FDibbins]

If I input the name of the file I want to refer to in cell A2, can I make the formula (pathway) update when I change the value of A2?

yes, you would use INDIRECT(), but that only works if the referenced file is open...
=indirect("'["&A2&"",xlsls]Sheet1'!$A1"

 

[MPH88]

This helps. However, I'm trying to avoid having the files open.

I got my sheet working. I re-approached the problem and used a different solution. Thanks for the new trick, tho!

 

[FDibbins]

What did you use?

Another option might be to install the Morefunc add-in, which allows using c;losed workbooks. Find it here...
http://www.ashishmathur.com/tag/morefunc/