EMERGENCY - INDIRECT FUNCTION PROBLEM

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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



Wayne said:
I have a field say (A1) containing a date. Cell (A2) is a date that is
Click to expand...
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
Click to expand...
seem to convert it into TRUE TEXT. Which is required when using the above
equation.
 
Back
Top