use a formula to reference a named range

  • Thread starter Thread starter ryanb.
  • Start date Start date
R

ryanb.

Say I have the following dynamic named ranges (12 per year, but here are
three):

TERR_2004
CAT_2004
MONTH_2004
TERR_2003
CAT_2003
MONTH_2003
and so on and so on back to 1999


Lets say in cell A2 is Data validation list with the years 1999-2004

Is it possible to put a formula in cell B2 that would be able to reference
the appropriate dynamic range based on the year value in A2? I did a google
search on the indirect() function, but I am unfamiliar with that function
and was not able to get any of the examples to work. I am hoping this is
possible as the only other options I see is trying to have IT get the data
in one big chunk, but they have told me it will be painful (due to changes
in systems/platforms/etc?), or manually move the data.

TIA
 
Upon further research, I was using INDIRECT() incorrectly
In case anyone was curious, I initially had the formula:

=INDIRECT("TERR_")&$A$2

which was returning #REF!

by simply moving the end paren, it works

=INDIRECT("TERR_"&$A$2)

sorry if I wasted anyone's time.

Thanks,
 
Back
Top