MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Changing a Text String into a Worksheet!Cell Reference


Posted by Shaun Brown on October 10, 2001 7:09 PM

Basically I'm attempting to concatenate a couple of variables into a cell reference.

For instance

Cell A1- October

Cell A2- =A1&"01"&"!D4" which equals October01!D4 as a text string

Is there a function that would change the text string value of A2 into an actual reference to October01!D4 ??? I do realize that it would be easier just to actually reference A2 to =October01!D4 but I have many cells that have to reference a previous worksheet. When I copy One worksheet, I just want to Change field A1 and have all the references change to another worksheet automatically.

Hopefully I made some sense.

Thanks in Advance

Shaun


Posted by Aladin Akyurek on October 10, 2001 11:03 PM

Shaun,

In A2 enter: ="'"&A1&"01"&"'!"&"D4"

You can retrieve the value in October01!D4 by using:

=INDIRECT(A2)

Aladin

=====

Posted by Shaun Brown on October 11, 2001 9:07 AM

Thank you! (nt)