Indirect and user selected workbook/worksheet

Posted by Fred on June 02, 2001 12:36 PM

Maybe the problem is simple, but I can't solve it.

In a file, let's call it File-A.xls, I have

A1= File-B
A2= Sheet1

Then, on the same page...

A5 =INDIRECT("["&$A$1&"]"&$A$2&"!D10")

So, if I write "Sheet2" in A2, I have the content of sheet2 in A5.

BUT....

THIS ONLY WORKS IF File-B is OPEN. If it's not, I have a #REF! error message.

I would like it to work even when the second file is closed. As it works with the address directly in the formula (=[TheFile.xls]theSheet!A1), it should also works here...

But I must be missing something.

Please, Help, Somebody... :-)

Posted by Kevin James on June 02, 2001 3:21 PM

Known issue, see link

http://support.microsoft.com/support/kb/articles/Q213/9/33.ASP?LN=EN-US&SD=gn&FR=0&qry=indirect&rnk=2&src=DHCS_MSPSS_gn_SRCH&SPR=XLW97

Posted by Kevin James on June 02, 2001 4:07 PM

work-around

If you know VBA, you could write the code to assemble the formula for you and insert it into the formula cell.

I am assuming you have more than just one cell that references other files. In that case you can create a loop that will update the column (row?) of cells needing the formula.

Kev

Posted by Rob on June 12, 2001 2:25 PM

I am working (trying to) with the same strugle. I did not find any solution NOT using vb.

Any help is indeed appreciated.

regards

rob