IMPOSSIBLE--VARIABLE WORKSHEET REFERENCE

XLDOESNOTWORK · Apr 29th, 2002, 03:40 PM

Can someone make this work? Is it possible?
I need to make a reference to the particular worksheet, but I want the name of the worksheet in the reference depend on the input in lets say A1. For example if I type "sheet 35" in A1, then all my references will be pointing to the sheet named "sheet 35". Anotherwords I am trying to make this work [=variableworksheetname!B1], where variableworksheetname is the name that I define in A1 of the current worksheet. SO all I would have to do is to put a new name/variable in A1, and it will automatically will get info from B1 of that name/variable/worksheet. The purpose of doing this is to make it easier to update all the reffering links to the other sheets without retyping the formulas in each of the references. I would be interested in any alternative ways of solving this problem. Just imagine that you have 1000 worksheets and you would like to extract specific information from the worksheet's name that you spacify in A1. Thanks a lot guys!!!!

Jay Petrulis · Apr 29th, 2002, 03:50 PM

=INDIRECT(A1&"!B1")

Chris Davison · Apr 29th, 2002, 04:12 PM

only to augment Jay's useful suggestion,

one drawback of =INDIRECT is that it needs your referenced worksheets to be open

so if you do have 1000 of them, pray to the Gods of RAM and get in early

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 29th, 2002, 03:40 PM	#1
XLDOESNOTWORK New Member Join Date: Apr 2002 Posts: 1	Can someone make this work? Is it possible? I need to make a reference to the particular worksheet, but I want the name of the worksheet in the reference depend on the input in lets say A1. For example if I type "sheet 35" in A1, then all my references will be pointing to the sheet named "sheet 35". Anotherwords I am trying to make this work [=variableworksheetname!B1], where variableworksheetname is the name that I define in A1 of the current worksheet. SO all I would have to do is to put a new name/variable in A1, and it will automatically will get info from B1 of that name/variable/worksheet. The purpose of doing this is to make it easier to update all the reffering links to the other sheets without retyping the formulas in each of the references. I would be interested in any alternative ways of solving this problem. Just imagine that you have 1000 worksheets and you would like to extract specific information from the worksheet's name that you spacify in A1. Thanks a lot guys!!!!

Apr 29th, 2002, 03:50 PM	#2
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	=INDIRECT(A1&"!B1")

Apr 29th, 2002, 04:12 PM	#3
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	only to augment Jay's useful suggestion, one drawback of =INDIRECT is that it needs your referenced worksheets to be open so if you do have 1000 of them, pray to the Gods of RAM and get in early