How can dynamically set a range name in a HLOOKUP command?

[Guest]

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).

 

[Chip Pearson]

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)