Chart with Dynamic Range not Working

Seba Robles

Board Regular
Joined
May 16, 2018
Messages
71
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hello,

I've created a chart for which I need a dynamic range since I will be adding weeks to the right and can't seem to make it work. I am not using tables and prefer to use formulas.

1609250136435.png


I created a dynamic named range called TEST for the data in A1:Q19 using the following formula: =OFFSET(Sheet1!$A$1,0,0,19,COUNTA(Sheet1!$1:$1))

When I select the data source of the chart and change the data source to the dynamic range (Sheet1!TEST) the chart takes the correct data. But whenever I add a new week the chart is not updated and when I go back to check the data source, it is no longer using the dynamic named range, it goes back to A1:Q19.

Any idea how I can make this work?

Thanks in advance!!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Hello,

I've created a chart for which I need a dynamic range since I will be adding weeks to the right and can't seem to make it work. I am not using tables and prefer to use formulas.

View attachment 28723

I created a dynamic named range called TEST for the data in A1:Q19 using the following formula: =OFFSET(Sheet1!$A$1,0,0,19,COUNTA(Sheet1!$1:$1))

When I select the data source of the chart and change the data source to the dynamic range (Sheet1!TEST) the chart takes the correct data. But whenever I add a new week the chart is not updated and when I go back to check the data source, it is no longer using the dynamic named range, it goes back to A1:Q19.

Any idea how I can make this work?

Thanks in advance!!
As far as I am aware, you will need to use a dynamic range for each individual series for this to work.
 
Upvote 0
I thought of that but want to avoid it if possible.

Does anyone else have another suggestion?
 
Upvote 0
Alternatively, I’d suggest you try changing the range to a table.
Then the chart should update dynamically as you expand the table.
 
Upvote 0
Solution
I ended up converting the range into a table

Thank you
I have been able to do similar with dynamic range at work. I will see if I can find an earlier example in the New Year. In the meantime, pleased that converting to a table worked as a workaround,
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top