Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: struggling with indirect()

  1. #1

    struggling with indirect()



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

    Hi there,

    i'm kinda of struggling here...
    I have a cell with a tab name/cell (string).

    I'm trying to access some data in another the file via a vlookup

    something like this, where the tab name is dynamic:

    =vlookup(xxx, [filename]tabname!A1, 1)

    but can't figure how to use indirect() function.
    any suggestion?

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    A couple of things....

    First, and most importantly, INDIRECT() won't work when referencing another workbook that is closed.
    Second, if you are okay with the other workbook being open, then ..... it is hard to tell if you even need VLOOKUP here. there is no column range reference in your sample, can you clarify what the VLOOKUP() would look like if you were not trying to use INDIRECT() and then what exact part is going to be Indirectly referenced and where that info is located.


  3. #3
    Hi,

    thanks for your feedback. Let me be more precise and give you a concrete example.

    in file 1, I select a retailer from a list.
    in file 2, I have a spreadsheet with columns representing retailers) and rows with product references. In each column, I have quantities.

    in file 1, I want to get the bestseller using large() function.
    Therefore the column number (representing the retailer) should be dynamic.

    and you're right, in this case no need for vlookup()

  4. #4
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Probably easier to use OFFSET
    =LARGE(OFFSET(Sheet2!A:A,0,B2),1) where B2 is the column number you want and presuming column A is product name

  5. #5
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Otherwise Use RC notation such as
    =Large(INDIRECT("Sheet1!RC"&B2,FALSE),1)

    Some gotcha's to avoid:
    1: Need the FALSE to say it is RC style
    2: Need the Quotes by indirect

  6. #6
    Quote Originally Posted by WizzardOfOz View Post
    Probably easier to use OFFSET
    =LARGE(OFFSET(Sheet2!A:A,0,B2),1) where B2 is the column number you want and presuming column A is product name
    Thanks, this seems to work. I'm getting the largest quantity, but how do I get the corresponding product ref? which is indeed in column A?

  7. #7
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    I like to try to avoid the volatile functions like INDIRECT and OFFSET if I can....

    This is my version:

    =INDEX(Sheet2!A:A,MATCH(LARGE(INDEX(Sheet2!B:K,,B2),1),INDEX(Sheet2!B:K,,B2),0))

    where B:K represent the columns that contain the information in Sheet2.


  8. #8
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    here's a good article on why to avoid volatile functions.
    http://chandoo.org/wp/2014/03/03/han...-are-dynamite/

    Basically indirect and offset always recalculates whereas INDEX and MATCH only on the cell change.

    Thanks NBVC for the tip

  9. #9
    Quote Originally Posted by NBVC View Post
    I like to try to avoid the volatile functions like INDIRECT and OFFSET if I can....

    This is my version:

    =INDEX(Sheet2!A:A,MATCH(LARGE(INDEX(Sheet2!B:K,,B2),1),INDEX(Sheet2!B:K,,B2),0))

    where B:K represent the columns that contain the information in Sheet2.

    This worked ! thanks so much.
    Now another question. If I want to access a specific cell in a specific tab in my sheet2 ? the tab and the cell would be dynamic.

  10. #10
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    So you want the same formula as above, only that "Sheet2" will be in a cell, like B1 and you want to reference that?

    if so, then we are back with INDIRECT....

    e.g.

    =INDEX(INDIRECT("'"&B1&"'!A:A"),MATCH(LARGE(INDEX(INDIRECT("'"&B1&"'!B:K"),,B2),1),INDEX(INDIRECT("'"&B1&"'!B:K"),,B2),0))


Page 1 of 2 1 2 LastLast

Posting Permissions

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