Results 1 to 7 of 7

Thread: Using "Named range" in "Indirect Function"

  1. #1
    Neophyte anil's Avatar
    Join Date
    Jun 2018
    Posts
    4
    Articles
    0
    Excel Version
    2007

    Using "Named range" in "Indirect Function"



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,
    I am using the INDIRECT function to look up values from multiple sheets to populate a summary sheet. However, the location of the data I need is not the same across sheets, nor does it remain in the same location in any sheet as fresh data is added every now and then and for various other reasons. How do I name a range or a single cell and use the "Name" in INDIRECT function? Please help.

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    You should be able to reference a cell that contains the name of the range you want to reference.

    e.g.

    =VLOOKUP(C3,INDIRECT(D2),3,0)

    this would lookup the value in C3 and find it in a Named Range which you enter the name of into cell D2. This should return value in 3rd column of the named range where C3 matches value in first column of the named range.


  3. #3
    Neophyte anil's Avatar
    Join Date
    Jun 2018
    Posts
    4
    Articles
    0
    Excel Version
    2007
    Many thanks for your help. But, OK, I did not get that. You will need to be patient with me.
    Let me give you specifics. In its simplest form I am using the following formula in the summary sheet
    =INDIRECT("'"&$A7&"'!F5")

    In this $A7 contains the name of the sheet from which data is to be fetched.
    Column A contains the list of sheet names.
    F5 is the cell, in the sheet corresponding to A7, containing the data.

    The problem is that there are over a hundred sheets and the data is not always in F5 of the sheet.
    So I end up writing the formula for each sheet or creating a fixed reference point on each sheet.

    Now can I give F5 a name so that the same formula can be copied.
    I hope I have made myself clearer.
    Thanks again

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    Not sure if I understood correctly, but you can't name a cell in each sheet with the same name.

    One thing I can think of is naming the cell after the sheetname somehow, like say in Sheet 2 you name F5 as "Sheet2Cell", then in Sheet 3 you would name a different cell, say D6 as "Sheet3Cell"... and so on.

    Then the formula would be something like: =INDIRECT(A7&"Cell") copied down.

    Does that work?

    If not, post a sample workbook, and we can see if there is an alternative solution based on how you are set up.


  5. #5
    Neophyte anil's Avatar
    Join Date
    Jun 2018
    Posts
    4
    Articles
    0
    Excel Version
    2007
    Thanks again. For now this should do. But I might get back to you with a sample later in a quest for a better solution.

  6. #6
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    194
    Articles
    0
    Excel Version
    2010
    You can use the same name on multiple sheets, you just make the name local to the sheet. Then you can use:

    =INDIRECT("'"&A1&"'!test")

    where A1 contains the sheet name and 'test' is the name of the range on each sheet.
    This website wants to know your momentum - | Deny | | Allow |

  7. #7
    Neophyte anil's Avatar
    Join Date
    Jun 2018
    Posts
    4
    Articles
    0
    Excel Version
    2007
    Thanks Joe. I will try it out and get back to you.

Tags for this Thread

Posting Permissions

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