Excel Named Ranges

[myrandr]

I am stuck, and I am not sure if there is even a solution for my block.

This is what I have:

I have numerous named ranges. For example, I have named ranges for vendors, the named ranges are: Vend1Eval, Vend2Eval, Vend3Eval....etc.

I want the ability to set the appropriate named range in a formula for the user selected vendor.

Formula Example 1: =If(D7 = "Yes", Vend1Eval, "No Being Used")

What I would like to be able to do is have the 1 in Vend1Eval be a dynamic number so the named range in the formula will change based on user selection, something like this:

Formula Example 2: =If(D7 = "Yes", Vend & E7 & Eval, "No Being Used")

I know example 2 does not work but I am not sure if there is a way to have it work, without using VBA.

Any help would be appreciated.

 

[Fluff]

Try
=INDIRECT("Vend"&E7&"Eval")

 

[myrandr]

Fluff, THANK YOU! Works perfectly!

 

[Fluff]

You're welcome & thanks for the feedback