Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: IMPOSSIBLE--VARIABLE WORKSHEET REFERENCE

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!!!!

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =INDIRECT(A1&"!B1")

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •