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
