Update chart series to today

[Bandito1]

Hi all,

I have this chart that shows how many files are on my desk.
I use formulas to keep it up to date;

In column D it shows the amount of files currently on my desk.

Now my chart series need to get updated every day.
Today it's 7 february. This should be the end of the series of my chart
Tomorrow it's the 8 and then the chart should include that one two.

I saw some soluations but that doesn't work for me. The data is with formules and as you can see the formule is already presents for 8 february and so on.

Someone got some idea how the series of the chart can go to today and adjusts every day?

Tracker QA on the floor New.xlsm
	A	B	C	D
2	Month	In	Out	Desk
Sheet1

Tracker QA on the floor New.xlsm
	A	B	C	D
128	01-Feb-23	7	3	58
129	02-Feb-23	3	6	55
130	03-Feb-23	1	5	51
131	04-Feb-23	0	3	48
132	05-Feb-23	0	2	46
133	06-Feb-23	4	4	46
134	07-Feb-23	6	4	48
135	08-Feb-23	0	0	48
136	09-Feb-23	0	0	48
137	10-Feb-23	0	0	48
Sheet1

Cell Formulas
Range	Formula
B128:B137	B128	=IF(A128="","",SUMPRODUCT(--('Tracker QA on the Floor'!$J:$J=A128)))
C128:C137	C128	=IF(A128="","",SUMPRODUCT(--('Tracker QA on the Floor'!$L:$L=A128)))
D128:D137	D128	=D127+B128-C128

 

[Skybluekid]

Have a look at this

How to Create a Dynamic Chart Range in Excel

Learn how to create a dynamic chart range that instantly updates Excel charts when the data changes. Done using OFFSET and INDEX function.

trumpexcel.com

 

[Bandito1]

Thank you for your reply
Found that solution but in afraid that doesnt work

I dont want to manual add each new day to the table

As you can see I pulled down the dates to (not in this example) to 2024

I dont want to update the data manual. There is a formula in it that calculates the file in my desk for each day till like 2024

But when i make table from it the table shows more data then today and the end of chart is 48, 48, 48, 48 while it should stop by 46 cause that is today (was)

 

[awoohaw]

how many days do you want to see in your series range from today?
What kind of chart are you making? Which Series do you want on the chart?

Also, why do you have column name of Month, when the dates seem to be a sequence of days?

 

[awoohaw]

Here is one way. It uses the Today() function and VLOOKUPS. If your data gets very large the workbook will process slowly.

Mr Excel Questions 3.xlsm
	A	B	C	D	E	F	G	H	I
1	Month	In	Out	Desk		Month	In	Out	Desk
2	01-Feb-2023	7	3	0		02/09/2023	0	0	0
3	02-Feb-2023	3	6	0		02/08/2023	0	0	0
4	03-Feb-2023	1	5	0		02/07/2023	6	4	0
5	04-Feb-2023	0	3	0		02/06/2023	4	4	0
6	05-Feb-2023	0	2	0		02/05/2023	0	2	0
7	06-Feb-2023	4	4	0		02/04/2023	0	3	0
8	07-Feb-2023	6	4	0		02/03/2023	1	5	0
9	08-Feb-2023	0	0	0
10	09-Feb-2023	0	0	0
Sheet1

Cell Formulas
Range	Formula
F2	F2	=TODAY()
G2:I8	G2	=VLOOKUP($F2,$A$2:$D$543,COLUMNS($F$1:G$1),0)
F3:F8	F3	=F2-1
D2:D10	D2	=D127+B128-C128

sample chart:

 

[awoohaw]

Here is one way. It uses the Today() function and VLOOKUPS. If your data gets very large the workbook will process slowly.

Mr Excel Questions 3.xlsm
	A	B	C	D	E	F	G	H	I
1	Month	In	Out	Desk		Month	In	Out	Desk
2	01-Feb-2023	7	3	0		02/09/2023	0	0	0
3	02-Feb-2023	3	6	0		02/08/2023	0	0	0
4	03-Feb-2023	1	5	0		02/07/2023	6	4	0
5	04-Feb-2023	0	3	0		02/06/2023	4	4	0
6	05-Feb-2023	0	2	0		02/05/2023	0	2	0
7	06-Feb-2023	4	4	0		02/04/2023	0	3	0
8	07-Feb-2023	6	4	0		02/03/2023	1	5	0
9	08-Feb-2023	0	0	0
10	09-Feb-2023	0	0	0
Sheet1

Cell Formulas
Range	Formula
F2	F2	=TODAY()
G2:I8	G2	=VLOOKUP($F2,$A$2:$D$543,COLUMNS($F$1:G$1),0)
F3:F8	F3	=F2-1
D2:D10	D2	=D127+B128-C128

sample chart:
View attachment 84973

Ijust realized the formula in column C did not come through. Will repost...

 

[awoohaw]

Cell D2 references E1, which enforced the starting number from your worksheet ranges

Mr Excel Questions 3.xlsm
	A	B	C	D	E	F	G	H	I
1	Month	In	Out	Desk	54	Month	In	Out	Desk
2	01-Feb-2023	7	3	58		02/09/2023	0	0	48
3	02-Feb-2023	3	6	55		02/08/2023	0	0	48
4	03-Feb-2023	1	5	51		02/07/2023	6	4	48
5	04-Feb-2023	0	3	48		02/06/2023	4	4	46
6	05-Feb-2023	0	2	46		02/05/2023	0	2	46
7	06-Feb-2023	4	4	46		02/04/2023	0	3	48
8	07-Feb-2023	6	4	48		02/03/2023	1	5	51
9	08-Feb-2023	0	0	48
10	09-Feb-2023	0	0	48
Dynamic Series Base on TODAY()

Cell Formulas
Range	Formula
F2	F2	=TODAY()
G2:I8	G2	=VLOOKUP($F2,$A$2:$D$543,COLUMNS($F$1:G$1),0)
F3:F8	F3	=F2-1
D2	D2	=E1+B2-C2
D3:D10	D3	=N(D2)+B3-C3

 

[Bandito1]

I think this will work, perfect

 

[awoohaw]

Happy to help.
I also suggest you put the "Desk" line on the secondary axis. This will make the other two line stand out better, and not be so flat. If you don't know how to do that, please post again.

If you think a post has provided you with the solution please mark it as an answer.