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.
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 |