INDIRECT and Dynamic Named Ranges problem

[rossa]

I was brushing up on Dynamic named ranges and ran into an issue using INDIRECT. I started off creating three Columns named Jan, Feb and Mar in columns C through E, on row 2. Rows 3 through 6 contained numbers. I named each range based on the heading, in cells J2:J4 I enetred the range names to use in an INDEX formula.
I then used the following formula to do a sum for each range (A1 contained 1, 2 or 3 which represented Jan, Feb and Mar).
SUM(INDIRECT(INDEX(J2:J4,A1)))

So far so good, everything worked fine until I tried to make the range "Jan" dynamic.
I used the formula belwo in the name manager for Jan, for some reason whenever I select 1 in A1 the formula now gives me a #REF! error
OFFSET(Sheet4!$C$2,1,0,COUNTA(Sheet4!$C:$C)-1,1)
Does anyone know why changing (or attempting to) the range to dynamic causes this?

Also regarding the use of INDIRECT, I read it can slow things down, is CHOOSE a better function?

 

[markmzz]

Try this:

=SUM(CHOOSE(A1,Jan,Feb,Mar))

Note: you can use EVALUATE function too. You can create a name:

Month - Refers To: =EVALUATE(INDEX(Sheet1!$J$4:$J$6,Sheet1!$A$1))

And use the formula =SUM(Month)

Markmzz