How do I use formulas using dynamic names

[DannyP]

I am trying to create formulas that can calculate the values using a cell
reference to refer to a named range.
For example: HLOOKUP(H15,Demand_1,2,FALSE). "Demand_1" is a name i have
given to a table of data where the first row is a horizontal list of dates
and H15 is the date for which I want a value returned. I have 3 other named
ranges structured the same way and I'd like the formula to be dynamic so that
instead of writing the formula as I've shown above it references a single
cell reference on that sheet which would contain the Name. The purpose would
be so that I can quickly switch the calculations between forecasting
scenarios.
I've tried using the Index, Indirect and Offset functions but can't seem to
get them to work.
Any suggestions?

 

[Ashish Mathur]

Hi,

Try this

=HLOOKUP(H15,indirect(A3),2,FALSE)

cell A3 contains the named ranges

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com