How do I use formulas using dynamic names
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? |
How do I use formulas using dynamic names
Hi,
Try this =HLOOKUP(H15,indirect(A3),2,FALSE) cell A3 contains the named ranges -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "DannyP" wrote in message ... 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? |
All times are GMT +1. The time now is 02:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com