Automated Chart and Ranges

[hananak]

Dear All,

I really need help, I have checked on internet but can't seems to find an answer for my problem.

Basically I am looking for formulas where new Expense category will be added (not that frequently) in rows and in the columns new months will be added and a graphs will be linked to the data to show the run rate.

What I want is that the Graphs to be automatically updated based on the new information available (Expense Category and Months).

I would really appreciate your help.

Please see the attached picture for better understanding of the layout of the data.

Thanks in advance for your kind help.

 

[awoohaw]

What is a scenario of change? A new month, a new category, or both?

And can you paste (not an image, please) some of the raw data that makes up your data for the pivot?

 

[hananak]

What is a scenario of change? A new month, a new category, or both?

And can you paste (not an image, please) some of the raw data that makes up your data for the pivot?

Hi,

Thanks for your reply. The scenario is new month and not that often but new expense category as well. I have revised little bit and now there is a secondary axis for expense %. I have uploaded the picture, so it was to understand the final result.

Formula Required.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2
3
4	Expenditure	Apr-23	May-23	Jun-23	Jul-23	Aug-23
5	Expense 1	10	20	15	50	45			New months will be added automatically.
6	Expense 2	9	10	30	0	80
7	Expense 3	15	16	17	5	6
8		34	46	62	55	131
9
10	Revenue	100	200	350	150	500
11	Expense %	34%	23%	18%	37%	26%
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Sheet1

Cell Formulas
Range	Formula
E8:I8	E8	=SUM(E5:E7)
E11:I11	E11	=E8/E10

 

[awoohaw]

Okay, So to make the scenario data more complete I created a source data group in a TABLE.
This answers your first question. I'm still thinking on how to calculate the percentage of expenses compared to revenue question. But, it seems to me you may need to use Power Pivot or Power Query to create a measure for that, and then add a secondary axis. I am not proficient at measures.

Mr excel questions 60.xlsm
	D	E	F
13
14	My Guess as to SOURCE DATA
15	Expense Type	Date	Amount
16	Expense 1	2023-Apr	4
17	Expense 2	2023-May	5
18	Expense 2	2023-Jun	4
19	Expense 2	2023-Jul	4
20	Expense 1	2023-Aug	1
21	Expense 3	2023-Apr	2
22	Expense 1	2023-May	5
23	Expense 1	2023-Jun	3
24	Expense 3	2023-Jul	5
25	Expense 3	2023-Aug	3
26	Expense 1	2023-Apr	2
27	Expense 3	2023-May	4
28	Expense 3	2023-Jun	3
29	Expense 3	2023-Jul	1
30	Expense 2	2023-Aug	3
31	Expense 1	2023-Apr	3
32	Expense 1	2023-May	5
33	Expense 2	2023-Jun	3
34	Expense 1	2023-Jul	3
35	Expense 2	2023-Aug	5
36	Expense 2	2023-Apr	1
37	Expense 3	2023-May	1
38	Expense 3	2023-Jun	4
39	Expense 2	2023-Jul	4
40	Expense 3	2023-Aug	5
41	Expense 2	2023-Apr	3
42	Expense 2	2023-May	4
43	Expense 2	2023-Jun	4
44	Expense 3	2023-Jul	5
45	Expense 2	2023-Aug	3
46	Expense 2	2023-Apr	1
47	Expense 1	2023-May	1
48	Expense 3	2023-Jun	2
49	Expense 3	2023-Jul	2
50	Expense 3	2023-Aug	4
51	Expense 2	2023-Apr	3
52	Expense 1	2023-May	1
53	Expense 1	2023-Jun	5
54	Expense 1	2023-Jul	1
55	Expense 1	2023-Aug	4
56	Expense 1	2023-Apr	5
57	Expense 2	2023-May	5
58	Expense 3	2023-Jun	3
59	Expense 1	2023-Jul	3
60	Expense 3	2023-Aug	1
61	Expense 1	2023-Apr	1
62	Expense 3	2023-May	2
63	Expense 1	2023-Jun	2
64	Expense 1	2023-Jul	5
65	Expense 1	2023-Aug	2
Sheet5

I used this table to create a pivot chart. Pivot charts do not copy with the xl2bb add in.
And when creating a pivot chart a pivot table is automatically created.

Because there is a table, additional records can be added that will automatically update your pivot chart.

Mr excel questions 60.xlsm
	D	E	F
15	Expense Type	Date	Amount
16	Expense 1	2023-Apr	4
17	Expense 2	2023-May	5
18	Expense 2	2023-Jun	4
19	Expense 2	2023-Jul	4
20	Expense 1	2023-Aug	1
21	Expense 3	2023-Apr	2
22	Expense 1	2023-May	5
23	Expense 1	2023-Jun	3
24	Expense 3	2023-Jul	5
25	Expense 3	2023-Aug	3
26	Expense 1	2023-Apr	2
27	Expense 3	2023-May	4
28	Expense 3	2023-Jun	3
29	Expense 3	2023-Jul	1
30	Expense 2	2023-Aug	3
31	Expense 1	2023-Apr	3
32	Expense 1	2023-May	5
33	Expense 2	2023-Jun	3
34	Expense 1	2023-Jul	3
35	Expense 2	2023-Aug	5
36	Expense 2	2023-Apr	1
37	Expense 3	2023-May	1
38	Expense 3	2023-Jun	4
39	Expense 2	2023-Jul	4
40	Expense 3	2023-Aug	5
41	Expense 2	2023-Apr	3
42	Expense 2	2023-May	4
43	Expense 2	2023-Jun	4
44	Expense 3	2023-Jul	5
45	Expense 2	2023-Aug	3
46	Expense 2	2023-Apr	1
47	Expense 1	2023-May	1
48	Expense 3	2023-Jun	2
49	Expense 3	2023-Jul	2
50	Expense 3	2023-Aug	4
51	Expense 2	2023-Apr	3
52	Expense 1	2023-May	1
53	Expense 1	2023-Jun	5
54	Expense 1	2023-Jul	1
55	Expense 1	2023-Aug	4
56	Expense 1	2023-Apr	5
57	Expense 2	2023-May	5
58	Expense 3	2023-Jun	3
59	Expense 1	2023-Jul	3
60	Expense 3	2023-Aug	1
61	Expense 1	2023-Apr	1
62	Expense 3	2023-May	2
63	Expense 1	2023-Jun	2
64	Expense 1	2023-Jul	5
65	Expense 1	2023-Aug	2
66	Expense 4	2023-Sep	10
Sheet5

Clicking REFRESH the chart automatically updates:

The key is using tables. You can probably do this using range references but you would need to update your range with every new month and or new expense.