struggling with indirect()

srezzonico

New member
Joined
Sep 29, 2014
Messages
6
Reaction score
0
Points
0
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

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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.
 

srezzonico

New member
Joined
Sep 29, 2014
Messages
6
Reaction score
0
Points
0
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

New member
Joined
Sep 4, 2013
Messages
184
Reaction score
0
Points
0
Location
Australia
Excel Version(s)
Office 365
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

New member
Joined
Sep 4, 2013
Messages
184
Reaction score
0
Points
0
Location
Australia
Excel Version(s)
Office 365
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

New member
Joined
Sep 29, 2014
Messages
6
Reaction score
0
Points
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

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

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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.
 

srezzonico

New member
Joined
Sep 29, 2014
Messages
6
Reaction score
0
Points
0
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

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
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))
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
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))

I thought you were kidding us about not wanting to use INDIRECT :) :)

Great Formula!
 
Top