Using "Named range" in "Indirect Function"

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.

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.

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

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.

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.

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.

Thanks Joe. I will try it out and get back to you.