Excel Offset Function Dynamic Range Question

Hi everyone,
I have been using the offset formula to capture the data stored in rows (and when they get expanded the charts I am using automatically updates).
The formula I have been using has worked right up to the last row of my spreadsheet and is now missing the last cell in the row. It is almost as if the formula is saying -1 or something...

I have attached photos because I know this is hard to explain. Basically you can see that the formulas in the two photos are EXACTLY the same, yet the range in the first photo includes the cell in Column K, and yet the second photo (with the same formula) only goes to J..

Can anyone help me figure out why this is?
Thanks!

Originally Posted by ChemistB

Based on what you've shown us (an uploaded spreadsheet would be better (Go Advanced>Manage Attachments)) you probably have either a blank or a text value in row 53

Hi ChemistB,
I checked under the home tab while selecting that cell in Row 53, but it says "Number" not "Text". It is not blank because I can see the "498" showing. Is this what you mean? If not, how can I check?
I can't really upload the spreadsheet - it's for my boss!

Specifically, it looks like D53 is blank
Therefor it is not counted in the COUNT function which is used in the dynamic range to determine how many columns are included in the range.

Originally Posted by Jonmo1

Specifically, it looks like D53 is blank
Therefor it is not counted in the COUNT function which is used in the dynamic range to determine how many columns are included in the range.

You're right. When I put a 0 in D53 the formula corrected itself and the range now includes Column K. Thanks!
What I don't get however, is in other rows I have empty cells but it never affected the formula before?

You're welcome...

Originally Posted by d247

What I don't get however, is in other rows I have empty cells but it never affected the formula before?

Can you show an example of that?

My mistake - I just reviewed the row I thought was not being affected by the empty cell but it is being affected I just didn't notice.
So the answer was to change all empty cells to a 0 - Thank you both!

Have you tried using a formal table for your data? Tables expand as new data are appended.

Thanks everyone.

Hi all -
Thank you so much for your help.
I am trying to select a few different cells from multiple/different sheets in my workbook but name them in "Name Manager".
Then based on that "Name", you it as an axis in a chart.

So far I've tried entering the following in Name Manger to select the cells but nothing seems to be working when I select the name in the series for the axis in the chart:

=('2009'!$AA$9),('2010'!$AA$9)
='2009:2013'!$AA$9

For some reason I can't post a new thread...