IMPOSSIBLE--VARIABLE WORKSHEET REFERENCE

XLDOESNOTWORK

New Member
Joined
Apr 28, 2002
Messages
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!!!!
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top