OFFSET Formula Trouble

[Naoki]

Maybe someone can help me with this formula:

=OFFSET('NM On time'!$B$25,0,0,COUNTA('NM On time'!$B$25:$J$25),9)

I have defined a name for this formula, to utilize as a criteria rang
in a graph. Don't ask me why I can't just grab the data I need, I'
doing this for someone else, has something to do with altering th
data. Anyways, my problem is that this formula is meant to grab
"header" for a list of store numbers like so...

95 | 60 | 46 | 50 | 8 | 98 | 90 | 65 | 54

There are nine store numbers, 9 columns across.

Obviously, there is just 1 row containing this header.

The formula I am using is correctly calling the 9 columns I need, bu
it is ALSO counting 9 rows down, so I have a 9x9 area being called fro
this formula. Basically, I want a 9x1 area, 9 columns, 1 row. Changin
the final 9 value in my equation to 8 reduces the number of columns t
8, so I know that that value SHOULD be 9, and I can't seem to think wh
its also calling (coincidentally) 9 rows. Any help is appreciated

 

[Frank Kabel]

Hi
use:
=OFFSET('NM On time'!$B$25,0,0,1,9)

But if you hardcode the range this way why not simply use:
$B$25:$J$25

or do you mean:
=OFFSET('NM On time'!$B$25,0,0,1,COUNTA('NM On time'!$B$25:$J$25))

 

[Naoki]

Thank you again, I knew it was very simple (I think the trouble is th
assignments I'm recieving are half-correct formulas with too muc
information that I try to work from).

So I've used your first formula for my criteria range.

Now, for the actual plotted data, I need a formuliac solution tha
references the named range "we" just made (because its the same size a
the data I need to plot), but I need to have the chart always referenc
the last row before data was input (this being the most recent "week")
I know I've asked similar questions before, but now that I have th
criteria range named, how do I utilize that to get the last row o
data?

For instance:

=OFFSET(NMOnTimeWeekly,1,)

Would go to the next row, which is week 1, well.. I'm in week 32 now
and I'd like the chart to always reference the final row without goin
having to go into the chart each week. Any suggestions

 

[Frank Kabel]

Hi
try:
=OFFSET('NM On time'!$B$25,COUNTA('NM On time'!$B$25:$B$1000)-1,0,1,9)