Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can dynamically set a range name in a HLOOKUP command?
I'm working on a project the projects out 13 weeks AND reports historically
13 weeks back. Data and reporting is done annually. Hence, once I get into November I need to reference a named in range in the "2008" file instead of the a named range in the current file. I've tried "concatenate", which addes double quotes. The only solution I have is that each year you need to "hard-code change" the formulas. Ex: Hlookup(F2,'2007 forecast'.xls,85,false) I'd like "2007" to dynamically change to 'current year + 1' (ie"2008") if Year(current date) < year(forecast date). |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can dynamically set a range name in a HLOOKUP command?
You can use the INDIRECT function to build a string with the correct year.
INDIRECT translates a text string to a real cell reference. =HLOOKUP(F2,INDIRECT(IF(MONTH(NOW())=11, YEAR(NOW()),YEAR(NOW())+1)& " forecast.xls"),85, FALSE) "Ron" wrote in message ... I'm working on a project the projects out 13 weeks AND reports historically 13 weeks back. Data and reporting is done annually. Hence, once I get into November I need to reference a named in range in the "2008" file instead of the a named range in the current file. I've tried "concatenate", which addes double quotes. The only solution I have is that each year you need to "hard-code change" the formulas. Ex: Hlookup(F2,'2007 forecast'.xls,85,false) I'd like "2007" to dynamically change to 'current year + 1' (ie"2008") if Year(current date) < year(forecast date). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamically create range of rows | Excel Discussion (Misc queries) | |||
Setting Range Dynamically | Excel Discussion (Misc queries) | |||
Dynamically Changing HLOOKUP table | Excel Worksheet Functions | |||
Dynamically set a range? | Excel Worksheet Functions | |||
How to use HLOOKUP or other command to find a value within a range | Excel Worksheet Functions |