Forum Discussion
Can INDIRECT be used to dynamically alter the second range reference in a Named Range
Let me play with simple sample. Assume on Sheet2 we have
and in Sheet1 we'd like to find element form here on position defined in A3
Direct formula will be
=INDEX(Sheet2!$E$3:$E$15,$A$3)With INDIRECT it's like
=INDEX(INDIRECT($A$1& "!" & "$E$3:" & "$E$15"),$A$3)For the dynamic range, assuming first cell is fixed, we shall only receive an address of the last cell in the range. If we calculate in B1 the size of the range as
=COUNTA(INDIRECT($A$1&"!$E:$E"))we may substitute E15 as
=INDEX(INDIRECT($A$1& "!" & "$E$3:" & ADDRESS($B$1+2,COLUMN($E$3))),$A$3)Here it is assumed ranges for all sheets are in column E and we don't insert/delete columns in first sheet with formulas. When above will be bit more complex, but idea is the same.
That might work but it's not what my (unclear) question was looking for. Let me see if i can restate it.
I have a named range, using Insert....Name.... and in the dialog box the range is specified as shown here:
And I then use VLOOKUP to reference any number of cells in that named range by formulas such as =VLOOKUP($A23,CurrentActuals,C$5,0) That works perfectly.
The problem is that occasionally I add a row to the range "CurrentActuals" and have always been changing that last reference manually to "$U$60" or "SU$65" I am wanting to see if I can use INDIRECT to allow the named range to change its parameters.
So I created a formula in cell J1 that dynamically changes so it reads "$U$60" or "$U$65" and then refer to cell J1 it in the Define Name dialog box by means of the Function INDIRECT. The Dialog Box accepts the entry without objection, and shows as displayed here:
It totally accepts the entry of the INDIRECT function (In fact, in creating this image, I entered it all in lower case and then came back to clip the screen shot and as you can see it not only accepted it, but changed it to upper case. Suggesting to me that it would work. But it doesn't. And yet cell $J$1 reads exactly as the cell reference in the first image....
So my question is simply phrased perhaps in two parts: (1) Does INDIRECT work as part of a cell reference in a named range? (2) If so, is there a syntax that somehow I'm missing? Either in the Cell itself--e.g., should all of it be surrounded by quotation marks?--or in the spot in the Dialog box where the named range is specified?
Does this make more sense? It seems to me that it should be very straightforward, especially given that the dialog box accepted the use of INDIRECT......
@Sergei Baklan Hmm...that reply omitted an image I'd put in it. Here it is. This is the Named Range dialog box without the use of INDIRECT. And it works just fine, as noted.
