Hi all,

Using Excel 2010.

I would like to add 12 months worth of data based on user input assumeing user inputs a date that allows for 12 months of data and does not return an error.
I have it working with this formula:
=SUM(INDEX(nData,MATCH(E17,nDates,0),2):INDEX(nData,MATCH(E17,nDates,0)-12,2))

Where:
E17 - User input date
nData - Dynamic Named Range
nDates - Dynamic Named Range

The ,2 only only adds values from Column 2 of the dynamic named range
Now I would like to alter slightly so as additional data is added to the next column, it is added as well.

I tried COLUMNS(nData) which returns 3, but includes a column of dates.
I tried editing the dynamic named range

from
nData=Sheet2!$A$1:INDEX(Sheet2!$1:$65535,COUNTA(Sheet2!$A:$A),COUNTA(Sheet2!$1:$1))

to
nData=Sheet2!$B$1:INDEX(Sheet2!$1:$65535,COUNTA(Sheet2!$B:$B),COUNTA(Sheet2!$1:$1))

But now my formula is returning a #REF! error.

Any ideas?

thx
w