Define NAME Using Lookup

[redhots4]

I can't believe there isn't a post about this but I sure couldn't find one. Here's the challenge:

I have some formulas defined by names. For example, PM_EFFORT is the sum of all rows where PM is listed as the project resource. I have additional names defined for BA_EFFORT, TOTAL_EFFORT, Etc.

Based upon what a user selects in a cell, I want to use one of the names in a formula. For example, if the user chooses "PM", I want Excel to display the value of PM_EFFORT*C5+G5. If the user chooses "BA", Excel will display the value of BA_EFFORT*C5+G5. The VLOOKUP will return and display "PM_EFFORT" in a cell, but as soon as I apply PM_EFFORT*C5+G6 I get a #VALUE!.

Ideas? Thanks everyone!
Shawn

 

[T. Valko]

I can't believe there isn't a post about this but I sure couldn't find one. Here's the challenge:

I have some formulas defined by names. For example, PM_EFFORT is the sum of all rows where PM is listed as the project resource. I have additional names defined for BA_EFFORT, TOTAL_EFFORT, Etc.

Based upon what a user selects in a cell, I want to use one of the names in a formula. For example, if the user chooses "PM", I want Excel to display the value of PM_EFFORT*C5+G5. If the user chooses "BA", Excel will display the value of BA_EFFORT*C5+G5. The VLOOKUP will return and display "PM_EFFORT" in a cell, but as soon as I apply PM_EFFORT*C5+G6 I get a #VALUE!.

Ideas? Thanks everyone!
Shawn

Try something like this...

A1 = PM_EFFORT

Then:

=INDIRECT(A1)*C5+G5

Note that this will not work if the named range PM_EFFORT is defined using functions like OFFSET.