Using "Named range" in "Indirect Function"

anil

New member
Joined
Jun 18, 2018
Messages
4
Reaction score
0
Points
0
Excel Version(s)
2007
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.
 

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

anil

New member
Joined
Jun 18, 2018
Messages
4
Reaction score
0
Points
0
Excel Version(s)
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
 

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

anil

New member
Joined
Jun 18, 2018
Messages
4
Reaction score
0
Points
0
Excel Version(s)
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.
 

JoePublic

Super Moderator
Staff member
Joined
Sep 16, 2011
Messages
220
Reaction score
0
Points
16
Location
UK
Excel Version(s)
2016
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.
 

anil

New member
Joined
Jun 18, 2018
Messages
4
Reaction score
0
Points
0
Excel Version(s)
2007
Thanks Joe. I will try it out and get back to you.
 
Top