EMERGENCY - INDIRECT FUNCTION PROBLEM

[Wayne]

I have a field say (A1) containing a date. Cell (A2) is a date that is one day before the date in (A1) thus A2=A1-1.

I want to use the data in cell (A2) to lookup a cell reference say (A3) in a file whose name is (A2).xls.

I have tried the following without luck.

=INDIRECT(" ' "&A2&" ' !A3")

Note: There are no spaces, just wanted everyone to see the formula correctly.

I believe the problem is the format of the information in A2. I can't seem to convert it into TRUE TEXT. Which is required when using the above equation.

Any help would be MONETARILY appreciated, if it works.
Please respond via e-mail.

Thanks in advance.

 

[Chip Pearson]

Wayne,

Try something like the following:

=INDIRECT("'"&TEXT(A2,"mmm dd yyyy")&"'!A3")

Change the date format code to match your worksheet name.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

I have a field say (A1) containing a date. Cell (A2) is a date that is

one day before the date in (A1) thus A2=A1-1.

I want to use the data in cell (A2) to lookup a cell reference say (A3) in a file whose name is (A2).xls.

I have tried the following without luck.

=INDIRECT(" ' "&A2&" ' !A3")

Note: There are no spaces, just wanted everyone to see the formula correctly.

I believe the problem is the format of the information in A2. I can't

seem to convert it into TRUE TEXT. Which is required when using the above
equation.