Alternative to INDIRECT with dynamic named range

Hi Gurus

I tried to use INDIRECT to reference a dynamic named range, and discovered you can't do that. I understand the reason why (sort of) from doing some research online. What I didn't discover was an alternative. Is there one? I want to be able to reference a dynamic named range within a formula based on a value in a cell. I don't want to have to type the name of the range each time, or use nested IF's.

I don't have an example sheet to upload at this time, but I'm not an Excel noob, so some advice of things to investigate would be sufficient.

Thanks in advance

Not sure what kind of "INDIRECT with dynamic named range", but I tried to break it down as per my understanding:

Name range A1:A2 as "ColumnA"
Name range B1:B2 as "ColumnB"

Now I have text "Column" in D3, "A" in E3 and "Column" in D4, "B" in E4

Start to test by combine D & E to create a name in text.
=D3&E3
Then
=INDIRECT(D3&E3)
Now it shows #VALUE

In fact, the underlying value is range of {1;2} (with 1 & 2 in A1 & A2) if try to hit F9

Now test more by:
=SUM(INDIRECT(D3&E3)) = 3

Does it seem INDIRECT work with name range?

@bebo021999

The Named Ranges you have chosen are not 'dynamically' defined.

A 'dynamically' defined range would be, for example:

=$A$1:INDEX($A:$A,MATCH(88^88,$A:$A))

which would create a dynamic range from A1 up to the cell containing the last numeric within column A.

If this Named Range were called MyRange, and cell B1 contained the string "MyRange", then:

=SUM(INDIRECT(B1))

would produce a #REF! error, since, as the OP points out, dynamically defined Named Ranges cannot be passed to INDIRECT.

@gak67

You can get round this issue using EVALUATE (though note that this requires that the workbook be saved as macro-enabled), viz (continuing the example from above) a further definition:

Name: MyRangeEval
Refers to: =EVALUATE(Sheet1!$B$1)

After which you can enter:

=SUM(MyRangeEval)

within the worksheet.

Regards

@XOX LX
That was my initial thinking (we know that creating a dynamic range is not unusual),
untill I read:
" but I'm not an Excel noob, " also, his posts said "1163"

that drove me into "over deep thinking"

@XOR LX - thanks for that. Despite being an Excel user for over 30 years, and contributing 1163 posts to this forum, I've never used the EVALUATE function. I'll do some investigation and try it out. The file is controlled by me (so much so that I am the only one with the password to modify) so saving it as a .xlsm file won't be an issue.

@bebo021999 - I like to think that I've helped others quite a bit with my 1163 posts. I used to think I was good with Excel, until I found this forum and realisd I still have a lot to learn.