Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

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


Check out our Excel Resources

Re: Changing a Text String into a Worksheet!Cell Reference

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

=====


Thank you! (nt)

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



This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.