Named Range Name in Cell Formula

[HighAndWilder]

Hi

Whilst I have a get around solution to this problem I wondered how others may implement the problem.

I want to refer to one of two named ranges in a formula using the RANK function in a cell. A value in another cell in the same row is used to determine which
named range is used.

How can I use an expression that returns the name of the named range?

I have tried using INDIRECT but it does not work.

For example :

=IF($B9=P$7,RANK($I9,reference_to_named_range),0)

Thanks

 

[Special-K99]

"I want to refer to one of two named ranges in a formula using the RANK...A value in another cell in the same row is used to determine which named range is used.

You have two named ranges, lets call them Rang1 and Rang2

Isn't this just

RANK($I9,IF($B9=P$7,Rang1,Rang2),0)

 

[HighAndWilder]

"I want to refer to one of two named ranges in a formula using the RANK...A value in another cell in the same row is used to determine which named range is used.

You have two named ranges, lets call them Rang1 and Rang2

Isn't this just

RANK($I9,IF($B9=P$7,Rang1,Rang2),0)

This was how I was doing it but the named range needs to be hard coded into the formula.

I wanted to find a way in which the name of the named range can be arrived at by evaluating an expression.

I needed to convert a reference assembled as text into a proper reference.

I can use the INDIRECT function but this does not seem to work if the named range is a dynamic one using OFFSET or if the named range refers to non-contigous cells.

It was the latter I wanted to do.

Do these observations run true in your experience?

 

[Special-K99]

I havent much experience of named ranges.