Chart must be manually updated :-(

[anderma8]

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?

 

[Toadstool]

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
	A	B	C	D	E	F
1	Week	Widget Sales			Week	Widget Sales
2	1	33			1	33
3	2	44			2	44
4	3	55			3	55
5	4	11			4	11
6	5	23			5	23
7	6	8			6	8
8	7	8			7	8
9
10
11
12
Sheet1

Cell Formulas
Range	Formula
E2:F12	E2	=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]]))),"")

 

[KOKOSEK]

It can be done with use of dyamic range of data.

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

then change data series on chart like below:

Chart automatically expand if another data for week appear.