Help with OFFSET Function

[brunking]

I am in dire need and have spent hours trying to get this right...

I need to pull the latest 25 data cells in a row for use in a graph. The data is set up like this starting in cell D76:

"Jan-03" "Feb-03"...."Sep-10"
*blank*
*blank*
*blank*
"33.51" "36.60"...."74.71"

I need to have it so it automatically pulls the last 25 data cells - i.e. the cells from Aug-08 to Sep-10. When I add data next month, I need it to shift to Sep-08 to Oct-10.

Currently, this is what I am trying but it keeps giving me error messages and I don't see where I am going wrong. *Note: using the Name manager*

Name: TwoYear
=OFFSET(Cost!$D$73,3,0,1,COUNTA(Cost!$76:$76))

Name: CostTwoYear
=OFFSET(TwoYear,4,0)

Any help on this coding would be extremely useful as I have spent 4 hours trying to solve this and looking at different tutorials.

 

[Domenic]

=OFFSET(Cost!$D$73,3,0,1,COUNTA(Cost!$76:$76))

Shouldn't COUNTA be referring to Row 73? If Row 76 contains no values, COUNTA returns 0. Consequently, OFFSET will return #REF!.

 

[JoeMo]

I am in dire need and have spent hours trying to get this right...

I need to pull the latest 25 data cells in a row for use in a graph. The data is set up like this starting in cell D76:

"Jan-03" "Feb-03"...."Sep-10"
*blank*
*blank*
*blank*
"33.51" "36.60"...."74.71"

I need to have it so it automatically pulls the last 25 data cells - i.e. the cells from Aug-08 to Sep-10. When I add data next month, I need it to shift to Sep-08 to Oct-10.

Currently, this is what I am trying but it keeps giving me error messages and I don't see where I am going wrong. *Note: using the Name manager*

Name: TwoYear
=OFFSET(Cost!$D$73,3,0,1,COUNTA(Cost!$76:$76))

Name: CostTwoYear
=OFFSET(TwoYear,4,0)

Any help on this coding would be extremely useful as I have spent 4 hours trying to solve this and looking at different tutorials.

I assume you're using these names to capture the X- and Y-values for a chart. If so, note that if they are defined at the worksheet level you must precede them with the sheet name like so:

=SERIES(,Cost!TwoYear,Cost!CostTwoYear,1)

In any case, I have found that I can only get your offset formulas to go into a graph series if I change 'CostTwoYear' to something else like 'yVals'.
Why this is, is a mystery to me, but you might want to try it.

 

[JoeMo]

I forgot to add to my earlier post that if the names are scoped at the workbook level, you still enter them with sheet name prefixes (as shown in earlier post), when you hit enter Excel will replace the sheet name with the workbook name and file extension.

 

[Marcelo Branco]

Hi

For Excel 2007 and assuming the costs are in Row 77 beggining in D77, try this (if they are in a different row, adjust the ranges)

CostTwoYear
=OFFSET(Cost!$D$77,0,COUNTA(Cost!$D$77:$XFD$77)-25,1,25)

Assuming the dates are in Row 73 beggining in D73
TwoYear
=OFFSET(CostTwoYear, -4, 0)

This way you can fill up the headers for all months till whatever you want.
The counting changes only when you put a new cost in row 77 and then both ranges are automatically updated.

For Excel 2003 change $XFD$77 to $IV$77

Hope this helps

M.