Dynamic reference to named formula returns #REF

[Hammer_757]

I’m trying to use a dynamic reference to named formulas. Each formula has a Name ending with a number ATEST1, ATEST2 etc. I am referring to the named formulas using the indirect function.

Please Login or Register  to view this content.

However, when I do I get a #REF error.

Things I have tried;
Building a dynamic reference to a name returns valid results If the name is not a formula and just refers to a cell.

Building the dynamic reference to the name formula returns #REF.

Referring directly to the named formula returns a valid result.

I have simplified and duplicated this problem in the attached workbook.

If there is a way to pull this off please enlighten me.
Thanks
Robert

[shg]

From Help:

The INDIRECT function syntax has the following arguments:

Ref_text Required. A reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string. If ref_text is not a valid cell reference, INDIRECT returns the #REF! error value.

The word "cell" could more accurately be replaced by "range," but a named formula is not among the possibilities.

[tim201110]

Indirect does not work with dynamic ranges, because it sees argument as a string. Otherwise a dynamic range must be evaluated.
What do you want to achieve?

[dflak]

It looks like what you need is a user defined function that takes column A values as input. Then a case statement in the UDF can run the appropriate formulas.

[shg]

Or a CHOOSE formula.

[Hammer_757]

It looks like what you need is a user defined function that takes column A values as input. Then a case statement in the UDF can run the appropriate formulas.

Thanks, i will look into that!

[Hammer_757]

Or a CHOOSE formula.

I dont undetstand what you mean. Can you explain please?

Oh wait, perhaps you are pointing me to the CHOOSE function! I'm a little slow but I catch on eventually :D

[Hammer_757]

From Help:



The word "cell" could more accurately be replaced by "range," but a named formula is not among the possibilities.

Thanks for clearifying that!

[Hammer_757]

Thanks all, I was able to get what I needed using the CHOOSE function as hinted by shg.
I use

Please Login or Register  to view this content.

This is named SiteRngChu and in the other formulas that need one of the SiteRng# I inserted SiteRngChu making the other formulas a little less bloated.