Chart must be manually updated :-(

anderma8

New Member
Joined
Oct 29, 2008
Messages
36
Office Version
  1. 365
Platform
  1. Windows
I have a chart that is based off of some calculations based on the week number. Each week, I have the data brought into the spreadsheet but I can't figure out a way to dynamically update the chart. If I have the chart formula for all 52 weeks, it's normal until the first future week - it goes to 0. Each week, I must manually update the rows that the chart reflects.

There MUST be a way to dynamically reflect only the weeks covered... Any suggestions?
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,384
Office Version
  1. 2016
Platform
  1. Windows
Hi Anderma8,

I believe you're asking to suppress chart entries where there is no data. The only way I've managed to do this is by using a table. Something like this:

Anderma8.xlsx
ABCDEF
1WeekWidget SalesWeekWidget Sales
2133133
3244244
4355355
5411411
6523523
76868
87878
9  
10  
11  
12  
Sheet1
Cell Formulas
RangeFormula
E2:F12E2=IFERROR(INDEX(A$2:A$53,AGGREGATE(15,6,ROW($A$2:$A$53)-ROW($A$1)/($B$2:$B$53<>""),ROW()-ROW(Table1[[#Headers],[Week]]))),"")


1597317800036.png
 

KOKOSEK

Board Regular
Joined
Apr 8, 2019
Messages
245
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
It can be done with use of dyamic range of data.

Create range named 'Weeks' on A2:A52 then edit like on picture:

Capture.JPG


then change data series on chart like below:

Capture1.JPG


Chart automatically expand if another data for week appear.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,334
Messages
5,624,069
Members
416,010
Latest member
NJT

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