Create Cluster Chart that Updates Automatically with New Data

[coco5280]

I am working on a sheet where we are tracking a person by the number of issues during the year and the reason. I have included an example spreadsheet. Columns A-C will change depending on what is found during the review, but Column D has three options to choose from. Once a row has been updated, it will automatically change to match the format of the previous rows and auto-populate the information in Columns I-M. These columns also update to match the format of the previous rows, alphabetize the names, and count a total of times they appear on the list and how many times each of the reasons were the cause. One issue with this is that I had to add the formulas all the way to the bottom of the sheet which causes a lag anytime the spreadsheet is updated. The number of entries on the list are unknown at this time as it depends on how many issues we run into during the year - it could be 40 or it 300, we don't know.

My goal with this is to create a cluster column chart that updates anytime columns I-M are updated. I created a pivot table with the data that I then converted to a pivot chart. While I was able to get this to work the way that I intended, it takes forever for the everything to update. I want the x-axis to contain the name of the person, y-axis to be a range of 0-20 as a count, and the different legends to be the total and the three reasons.

I am wondering if there was a way shorten the time for everything to update by writing a VBA that generates both Columns I-M and the cluster column chart.

Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Address	ID #	Name	Reason					Name	Total # per Person	Busy	No Phone #	Moved
2	123 ABC Way		john.doe	Busy					jane.doe	3		2	1
3	1675 N Name St		jane.doe	No Phone #					john.doe	1	1
4	543 Merry Way		jane.doe	Moved
5	234 Nowhere Ln		jane.doe	No Phone #
6
7
8
9
10
11
12
13
Service Address Log

Cell Formulas
Range	Formula
I2:I3	I2	=SORT(UNIQUE(FILTER(C2:C1048576,LEN(C2:C1048576))))
J2:J13	J2	=COUNTIF($C$2:$C$1048576,I2)
K2:K13	K2	=COUNTIFS($C$2:$C$1048576,I2,$D$2:$D$1048576,K$1)
L2:L13	L2	=COUNTIFS($C$2:$C$1048576,I2,$D$2:$D$1048576,L$1)
M2:M13	M2	=COUNTIFS($C$2:$C$1048576,I2,$D$2:$D$1048576,M$1)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I2:M1048576	Expression	=AND($I2<>"",MOD(ROW(),2)=0)	text	NO
I2:M1048576	Expression	=AND($I2<>"",MOD(ROW(),2)=1)	text	NO
A2:D1048576	Expression	=AND($A2<>"",MOD(ROW(),2)=0)	text	NO
A2:D1048576	Expression	=AND($A2<>"",MOD(ROW(),2)=1)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D2:D13	List	Busy,No Phone #,Moved

 

[Peter_SSs]

Duplicate to: Create Cluster Chart that Updates Automatically with New Data

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.