Complicated formula...Please help if possible...

[vingo]

Here it is....

Work Book 1
a1 Adams
a2 =BD23 from Workbook TS, Adams worksheet

I want to be able to change a1 to another name (Smith) and a2 to =BD23 from Workbook TS, Smith Worksheet.

When I do a simple reference, it comes out as

='[TS.xls]Adams'!$BD$23

I need the Adams! to change depending on what I put in A1. I've been working on this for almost 2 hours now. PLEASE HELP!!! THANKS!!

 

[Aladin Akyurek]

=INDIRECT("'[TS.xls]"&A1&"'!BD23")

which requires that TS.xls is open.

 

[vingo]

Hi Aladin,

Thanks for the quick reply. I tried that formula but I'm getting #REF!...Does it matter that BD23 is also a formula? Thanks again for helping...

Vi

 

[vingo]

Hi Aladin,

It did work. How I reference it to a workbook that is not open?? TS.xls is in D:\

Thanks again!!!

Vi

 

[Aladin Akyurek]

Hi Aladin,

It did work. How I reference it to a workbook that is not open?? TS.xls is in D:\

Thanks again!!!

Vi

Since you are referencing a single cell, try INDIRECT.EXT of the morefunc.xll add-in.