Problem with Named Range 1

[shelby55]

Hello

I'm using Excel 2010. I have a data set where the data is per month of activity. I want to use offset so that the graphs will change dynamically as I enter in data each month.

I have a date range from A4 to A31 and the current named range is:
=OFFSET(M1B!$A$4,0,0,COUNTA(M1B!$A:$A)-1,1)

I've used the same formula above for the data points that are in columns B, C and D and they work very well, only showing the data in the cells that have data. But the formula for date range shows the range as two below the current. So right now the data is from A4 to A31 but the named ranged shows as A4 to A33. If I add data into A32 then the range shows as A4 to A34 with A33 and A34 having no data. I did a hard delete of rows past A31 to see if maybe I had typed something in those cells which was causing issues but that isn't the case. Because the other named ranges are working as they should I am unclear why this would be causing issues so hopefully someone can help? Thanks very much.

 

[SkipVought]

It's counting ALL cells with data in column A. I'd guess that you might have DATA CHARACTERS somewhere above A4 or below A33 maybe a SPACE.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

You're amazing, you are correct that I have data above the header. I adjusted to:
=OFFSET(M1B!$A$4,0,0,COUNTA(M1B!$A:$A)-3,1)

and it works but I just couldn't figure out why it wasn't working like the others and of course they don't have anything above their headers so now I know and I'm confident it will work as required. Thanks Skip!!

 

[Deniall]

If you want to make it slightly less dependent upon whether you have data in cells above $A$4, try
=OFFSET(M1B!$A$4,0,0,COUNTA(M1B!$A:$A)-COUNTA(M1B!$A$1:$A$4)+1,1)
or some minor variant thereof.