Update Chart to include last row data using defined range

Question

I have a question , similar to this VBA: Modify chart data range But I the answer was based on horizontal data set.

How can I make this named range update to include latest row on a vertical column set ?

This is what I got as my formula, B1 being the header, and Column B will have all my data.

=OFFSET(Sheet1!$B$1,,,1,COUNTA(Sheet1!$B2:$B))

Edit

I am using the following defined range now:

=OFFSET(Sheet1!$B$2,,,COUNTA(Sheet1!$B2:$B1000),1)

but when I add it to my chart I get the following error:

Justyna MK · Accepted Answer · 2018-12-10 23:02:54Z

1

You're almost there - you basically need to swap height parameter with width: =OFFSET(Sheet1!$B$1,,,COUNTA(Sheet1!$B1:$B1000),1)

Here's a range that excludes header in B1: =OFFSET(Sheet1!$B$2,,,COUNTA(Sheet1!$B2:$B1000),1)

Of course you're welcome to extend the B1:B1000 range. Hope it helps!

answered Dec 10, 2018 at 23:02

Justyna MK

3,5633 gold badges15 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

excelguy Over a year ago

Thanks, for some reason I cannot add this named range to my chart. I am getting error . I'll add it to question.

Justyna MK Over a year ago

I see. This must be happening because of lack of absolute references in Named Range formula. Once you start selecting other cells / playing around with the chart, the range will change (i.e. shift to your current selection) and thus it will stop working properly. Try adding absolute references to all cells: =OFFSET(Sheet1!$B$2,,,COUNTA(Sheet1!$B$2:$B$1000),1)

Justyna MK Over a year ago

Could you let me know what is the result of your COUNTA formula? =COUNTA(Sheet1!$B$2:$B$1000) Another check might be to go to your Name Manager, select your Name and then select inside of the "Refers to" window - it should highlight the range which is represented by this formula.

excelguy Over a year ago

Interesting, when I enter in the formula ,=COUNTA(Sheet1!$B$2:$B$1000) the open file dialog comes up. then I click cancel and its a value of 1.. btw my sheet name isnt actually Sheet1.

Justyna MK Over a year ago

Thanks for checking - this might be the issue. Try updating the whole formula and replace Sheet1 with your sheet name, for example: =OFFSET('My Sheet'!$B$2,,,COUNTA('My Sheet'!$B$2:$B$1000),1) Then repeat the checks we just discussed (check the result of COUNTA formula and check the range that is selected in Name Manager > Refers To)

|

Collectives™ on Stack Overflow

Update Chart to include last row data using defined range

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related