Ignore Data for Dynamic Range
 

Excel 2003 - The date runs horizontal, left to right, on the columns. There
are two indicators for data: The goal, which will stay somewhat constant but
might change from year to year and the monthly data indicator (lets call it
Temp) . Both the date and the goal are set for years ahead. The Temp is what
I want the dynamic range to reference. I want to see the last 12 months based
on the last temp enteredand this part works:
=OFFSET(Sheet1!$C$4,1,COUNTA(Sheet1!$C$5:$BA$5)-12,1,12).
If I don't have the dates and Goal set ahead of time I can just use the same
method for Date and goal, however I will be setting this spreadsheet up for
use by many people and I don't want them to possibly mess up the date or
goal. The goal seems to follow along nicely with the temp and at this point
is not problem. What is tripping me up is the date. My dynamic range is
keying on the last date entered. How do I show the date to coorispond to the
temp but key on the last temp entered and show a range of 12?
Dale

Ignore Data for Dynamic Range
 

I figured it out. Just some minor changes to which column is referenced.

Temp
=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$B:$B)-12,1,12,1)

Dates
=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$B:$B)-12,0,12,1)


"dale" wrote:

Excel 2003 - The date runs horizontal, left to right, on the columns. There
are two indicators for data: The goal, which will stay somewhat constant but
might change from year to year and the monthly data indicator (lets call it
Temp) . Both the date and the goal are set for years ahead. The Temp is what
I want the dynamic range to reference. I want to see the last 12 months based
on the last temp enteredand this part works:
=OFFSET(Sheet1!$C$4,1,COUNTA(Sheet1!$C$5:$BA$5)-12,1,12).
If I don't have the dates and Goal set ahead of time I can just use the same
method for Date and goal, however I will be setting this spreadsheet up for
use by many people and I don't want them to possibly mess up the date or
goal. The goal seems to follow along nicely with the temp and at this point
is not problem. What is tripping me up is the date. My dynamic range is
keying on the last date entered. How do I show the date to coorispond to the
temp but key on the last temp entered and show a range of 12?
Dale