Chart with Dynamic Range not Working

Seba Robles

New Member
Joined
May 16, 2018
Messages
43
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!!
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

JB2020

Board Regular
Joined
Jul 29, 2020
Messages
75
Office Version
  1. 365
  2. 2016
  3. 2010
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.

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.
 

Seba Robles

New Member
Joined
May 16, 2018
Messages
43
I thought of that but want to avoid it if possible.

Does anyone else have another suggestion?
 

Xl365

New Member
Joined
Dec 29, 2020
Messages
10
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
Alternatively, I’d suggest you try changing the range to a table.
Then the chart should update dynamically as you expand the table.
 
Solution

Xl365

New Member
Joined
Dec 29, 2020
Messages
10
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
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,
 

Watch MrExcel Video

Forum statistics

Threads
1,123,253
Messages
5,600,544
Members
414,387
Latest member
Vincent88

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
Top