MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Using Worksheet Names as a Variable for Formulas


Posted by James on November 21, 2001 8:09 AM

I've searched high and low for this one with no results...it's my Excel Holy Grail...hope someone can help:

I want to have a column of my worksheet names, and then use those worksheet names in a formula. This way, each row can reference the worksheet in column. This will allow a fill down or fill right to reference the worksheet I want instead of being stuck in the worksheet that is listed.

Here's what I want. Column A has the names of my worksheets. Column B has the formula that will use the value in Column A as the worksheet name and then the cell in that worksheet I want to grab.

---A-----B-------
1--WS1---"=A1!A1"
2--WS2---"=A2!A1"
3--WS3---"=A3!A1"

Obviously, this formula doesn't work (it asks for the file named A1, A2, or A3). But I hope it gets my point across.

Any help/advice is greatly appreciated!

James
woosleyj@usa.net


Posted by Juan Pablo on November 21, 2001 8:15 AM

Try in B1

=INDIRECT("'"&A1&"'!A1")

Juan Pablo

Posted by Mark W. on November 21, 2001 8:16 AM

ERR

Posted by James on November 21, 2001 8:44 AM

Juan,

I looked at INDIRECT before but must have missed something and gave up. With your help, I got it to work! Thanks!

However, the reference to A1 in the called worksheet is static and when I fill right, it only gives me A1, not A2...A3...

Is there a good way to make that a relative call?

James

Posted by Mark W. on November 21, 2001 8:48 AM

Use =INDIRECT("'"&A1&"'!"&CELL("address",A1)) (nt)

Posted by Juan Pablo on November 21, 2001 8:49 AM

Try with

=INDIRECT("'"&A1&"'!"&ADDRESS(ROW(),1))

Will return:

=Sheet1!A1
=Sheet2!A2
etc.

Juan Pablo Juan,

Posted by James on November 21, 2001 8:58 AM

Re: THANKS TO ALL

Great! Only had to add one thing for it to fill down...a $ in the last A1:

=INDIRECT("'"&A1&"'!"&CELL("address",A$1))

Everyone, thanks for the help! This is going to save me a TON of time! I always knew it was possible, just needed a push in the right direction. Learn something new everyday!

James