struggling with indirect()

[srezzonico]

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?

[NBVC]

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.

[srezzonico]

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()

[WizzardOfOz]

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

[WizzardOfOz]

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

[srezzonico]

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?

[NBVC]

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.

[WizzardOfOz]

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

[srezzonico]

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.

[NBVC]

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