+ Reply to Thread
Results 1 to 9 of 9

Referncing another cell from a named sheet with the sheet name coming from a list of names

  1. #1
    Forum Contributor
    Join Date
    06-25-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    179

    Referncing another cell from a named sheet with the sheet name coming from a list of names

    Hello,
    I have a list of sheets that are all names. I have the list of names and i need to grab one bit from each sheet.
    Is there anyway to do something like =B1!A6
    Where B1 is the name of the sheet and A6 is the value i need from the sheet.

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Referncing another cell from a named sheet with the sheet name coming from a list of n

    Perhaps

    =Indirect("B1!A6") ...adapt for others accordingly
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Forum Contributor
    Join Date
    01-24-2011
    Location
    Sheppey
    MS-Off Ver
    Excel 2010
    Posts
    239

    Re: Referncing another cell from a named sheet with the sheet name coming from a list of n

    Try - =SUM(INDIRECT(B1&"!A6"))

  4. #4
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Referncing another cell from a named sheet with the sheet name coming from a list of n

    Try this
    Please Login or Register  to view this content.
    Drag/Fill Down.

    Note the apostrophes in the formula to allow for possible spaces in the sheet name.

    See this workbook to see the results ... Is th1s what you mean?
    Attached Files Attached Files
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  5. #5
    Forum Contributor
    Join Date
    06-25-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    179

    Re: Referncing another cell from a named sheet with the sheet name coming from a list of n

    I get a reference error when i do this for some reason? on both Ace and Barry's versions. Its copying the name of the worksheet from a cell and drawing a piece of data from said sheet but i get a ref error. If i swap the B1 for just the exact copy from the cell it seems to work.

  6. #6
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Referncing another cell from a named sheet with the sheet name coming from a list of n

    Have you looked at the attachment in Post #4

    If this doesn't do as you need, then post a sample workbook illustrating your expectations.

  7. #7
    Forum Contributor
    Join Date
    06-25-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    179

    Re: Referncing another cell from a named sheet with the sheet name coming from a list of n

    hi couldnt get it to work here is an example sheet
    hereis an example

    test book for people.xls

  8. #8
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Referncing another cell from a named sheet with the sheet name coming from a list of n

    To get this to work the data you need to retrieve should be in the same position on each sheet, otherwise you will need to manually change each formula, in which case you might as well just use direct references.

    See this workbook
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    06-25-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    179

    Re: Referncing another cell from a named sheet with the sheet name coming from a list of n

    Hey thnks for that its working and its all in the right place now.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1