Hi,
I'm wondering if someone could help me with providing some automation to a workbook that I'm working on.
In this workbook we will be providing information and updates regarding tasks on multiple tab's. What I would like to happen is have it set up so that when the status (drop down menu) of a particular task is changed to "in progress" that task is copied to the summary tab (The row shouldn't be deleted just copied). Which leads to the next part of the problem. We will be constantly providing updated comments to tasks so I would also like a macro in place that would check to make sure when copying a row to the summary sheet there isn't a duplicate of that task ID already on the worksheet and if there is it would just copy the info to that same row copying over the old information on the summary tab.
I would then like to be able to have a macro that will remove the information from the summary sheet and place the row back on its original tab when the status has been changed to "completed" and similar to the previous one I would like coding in place that would check to make sure when copying a row to the original tab it came from there isn't a duplicate of that task ID already on the worksheet and if there is it would just copy the info to that same row copying over the old information on the original tab.
I'm wondering if someone could help me with providing some automation to a workbook that I'm working on.
In this workbook we will be providing information and updates regarding tasks on multiple tab's. What I would like to happen is have it set up so that when the status (drop down menu) of a particular task is changed to "in progress" that task is copied to the summary tab (The row shouldn't be deleted just copied). Which leads to the next part of the problem. We will be constantly providing updated comments to tasks so I would also like a macro in place that would check to make sure when copying a row to the summary sheet there isn't a duplicate of that task ID already on the worksheet and if there is it would just copy the info to that same row copying over the old information on the summary tab.
I would then like to be able to have a macro that will remove the information from the summary sheet and place the row back on its original tab when the status has been changed to "completed" and similar to the previous one I would like coding in place that would check to make sure when copying a row to the original tab it came from there isn't a duplicate of that task ID already on the worksheet and if there is it would just copy the info to that same row copying over the old information on the original tab.
Book1 | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | High | Medium | Low | STATUS TOTAL | % OF TOTAL | |||||||
2 | Not Started | 0 | 0 | 0 | 0 | 0% | ||||||
3 | In Progress | 0 | 0 | 0 | 0 | 0% | ||||||
4 | Complete | 0 | 0 | 0 | 0 | 0% | ||||||
5 | On Hold | 0 | 0 | 0 | 0 | 0% | ||||||
6 | Overdue | 0 | 0 | 0 | 0 | 0% | ||||||
7 | PRIORITY TOTAL | 0 | 0 | 0 | 0 | |||||||
8 | Complete table, below. Chart counts will populate automatically, above. | |||||||||||
9 | TASK ID | TASK | ASSIGNED TO | START DATE | END DATE | STATUS | PRIORITY LEVEL | CAMPUS | CATEGORY | COMMENTS | ||
10 | ||||||||||||
11 | ||||||||||||
12 | ||||||||||||
13 | ||||||||||||
14 | ||||||||||||
15 | ||||||||||||
16 | ||||||||||||
17 | ||||||||||||
18 | ||||||||||||
19 | ||||||||||||
20 | ||||||||||||
21 | ||||||||||||
22 | ||||||||||||
23 | ||||||||||||
24 | ||||||||||||
25 | ||||||||||||
Summary |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C2 | C2 | =COUNTIFS(F10:F83,"Not Started",G10:G83,"High") |
D2 | D2 | =COUNTIFS(F10:F83,"Not Started",G10:G83,"Medium") |
E2 | E2 | =COUNTIFS(F10:F83,"Not Started",G10:G83,"Low") |
F2:F6 | F2 | =SUM(C2:E2) |
G2 | G2 | =IFERROR(F2/F7,"0%") |
C3 | C3 | =COUNTIFS(F10:F83,"In Progress",G10:G83,"High") |
D3 | D3 | =COUNTIFS(F10:F83,"In Progress",G10:G83,"Medium") |
E3 | E3 | =COUNTIFS(F10:F83,"In Progress",G10:G83,"Low") |
G3 | G3 | =IFERROR(F3/F7,"0%") |
C4 | C4 | =COUNTIFS(F10:F83,"Complete",G10:G83,"High") |
D4 | D4 | =COUNTIFS(F10:F83,"Complete",G10:G83,"Medium") |
E4 | E4 | =COUNTIFS(F10:F83,"Complete",G10:G83,"Low") |
G4 | G4 | =IFERROR(F4/F7,"0%") |
C5 | C5 | =COUNTIFS(F10:F83,"On Hold",G10:G83,"High") |
D5 | D5 | =COUNTIFS(F10:F83,"On Hold",G10:G83,"Medium") |
E5 | E5 | =COUNTIFS(F10:F83,"On Hold",G10:G83,"Low") |
G5 | G5 | =IFERROR(F5/F7,"0%") |
C6 | C6 | =COUNTIFS(F10:F83,"Overdue",G10:G83,"High") |
D6 | D6 | =COUNTIFS(F10:F83,"Overdue",G10:G83,"Medium") |
E6 | E6 | =COUNTIFS(F10:F83,"Overdue",G10:G83,"Low") |
G6 | G6 | =IFERROR(F6/F7,"0%") |
C7:F7 | C7 | =SUM(C2:C6) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
I10:I83 | Cell Value | ="Strategic Initiative" | text | NO |
I10:I83 | Cell Value | ="Abutter Activity" | text | NO |
I10:I83 | Cell Value | ="Major Projects" | text | NO |
H10:H83 | Cell Value | contains "BUMC" | text | NO |
H10:H83 | Cell Value | contains "Fenway" | text | NO |
H10:H83 | Cell Value | contains "CRC" | text | NO |
I10:I83 | Cell Value | contains "Community Relations" | text | NO |
I10:I83 | Cell Value | contains " " | text | NO |
I10:I83 | Cell Value | contains "Design" | text | NO |
I10:I83 | Cell Value | contains "Community Relations" | text | NO |
I10:I83 | Cell Value | contains "Construction" | text | NO |
I10:I83 | Cell Value | contains "Program" | text | NO |
E1 | Cell Value | contains "Low" | text | NO |
E1 | Cell Value | contains "Medium" | text | NO |
E1 | Cell Value | contains "High" | text | NO |
D1 | Cell Value | contains "Low" | text | NO |
D1 | Cell Value | contains "Medium" | text | NO |
D1 | Cell Value | contains "High" | text | NO |
C1 | Cell Value | contains "Low" | text | NO |
C1 | Cell Value | contains "Medium" | text | NO |
C1 | Cell Value | contains "High" | text | NO |
B2:B6 | Cell Value | contains "Overdue" | text | NO |
B2:B6 | Cell Value | contains "On Hold" | text | NO |
B2:B6 | Cell Value | contains "Complete" | text | NO |
B2:B6 | Cell Value | contains "In Progress" | text | NO |
B2:B6 | Cell Value | contains "Not Started" | text | NO |
G10:I83 | Cell Value | contains "Low" | text | NO |
G10:I83 | Cell Value | contains "Medium" | text | NO |
G10:I83 | Cell Value | contains "High" | text | NO |
F10:F83 | Cell Value | contains "Overdue" | text | NO |
F10:F83 | Cell Value | contains "On Hold" | text | NO |
F10:F83 | Cell Value | contains "Complete" | text | NO |
F10:F83 | Cell Value | contains "In Progress" | text | NO |
F10:F83 | Cell Value | contains "Not Started" | text | NO |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
F10:F83 | List | ='Drop Downs'!$A$3:$A$8 |
G10:G83 | List | ='Drop Downs'!$C$3:$C$6 |
H10:H83 | List | ='Drop Downs'!$E$3:$E$6 |
I10:I83 | List | ='Drop Downs'!$G$3:$G$6 |
Book1 | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | High | Medium | Low | STATUS TOTAL | % OF TOTAL | |||||||
2 | Not Started | 0 | 0 | 0 | 0 | 0% | ||||||
3 | In Progress | 0 | 1 | 0 | 1 | 100% | ||||||
4 | Complete | 0 | 0 | 0 | 0 | 0% | ||||||
5 | On Hold | 0 | 0 | 0 | 0 | 0% | ||||||
6 | Overdue | 0 | 0 | 0 | 0 | 0% | ||||||
7 | PRIORITY TOTAL | 0 | 1 | 0 | 1 | |||||||
8 | Complete table, below. Chart counts will populate automatically, above. | |||||||||||
9 | TASK ID | TASK | ASSIGNED TO | START DATE | END DATE | STATUS | PRIORITY LEVEL | CAMPUS | CATEGORY | COMMENTS | ||
10 | 1.1 | Example | Example | 01/01/2022 | In Progress | Medium | CRC | Major Projects | ||||
11 | ||||||||||||
12 | ||||||||||||
13 | ||||||||||||
14 | ||||||||||||
15 | ||||||||||||
16 | ||||||||||||
17 | ||||||||||||
18 | ||||||||||||
19 | ||||||||||||
20 | ||||||||||||
21 | ||||||||||||
22 | ||||||||||||
23 | ||||||||||||
24 | ||||||||||||
25 | ||||||||||||
26 | ||||||||||||
Data Sciences - 1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C2 | C2 | =COUNTIFS(F10:F83,"Not Started",G10:G83,"High") |
D2 | D2 | =COUNTIFS(F10:F83,"Not Started",G10:G83,"Medium") |
E2 | E2 | =COUNTIFS(F10:F83,"Not Started",G10:G83,"Low") |
F2:F6 | F2 | =SUM(C2:E2) |
G2 | G2 | =IFERROR(F2/F7,"0%") |
C3 | C3 | =COUNTIFS(F10:F83,"In Progress",G10:G83,"High") |
D3 | D3 | =COUNTIFS(F10:F83,"In Progress",G10:G83,"Medium") |
E3 | E3 | =COUNTIFS(F10:F83,"In Progress",G10:G83,"Low") |
G3 | G3 | =IFERROR(F3/F7,"0%") |
C4 | C4 | =COUNTIFS(F10:F83,"Complete",G10:G83,"High") |
D4 | D4 | =COUNTIFS(F10:F83,"Complete",G10:G83,"Medium") |
E4 | E4 | =COUNTIFS(F10:F83,"Complete",G10:G83,"Low") |
G4 | G4 | =IFERROR(F4/F7,"0%") |
C5 | C5 | =COUNTIFS(F10:F83,"On Hold",G10:G83,"High") |
D5 | D5 | =COUNTIFS(F10:F83,"On Hold",G10:G83,"Medium") |
E5 | E5 | =COUNTIFS(F10:F83,"On Hold",G10:G83,"Low") |
G5 | G5 | =IFERROR(F5/F7,"0%") |
C6 | C6 | =COUNTIFS(F10:F83,"Overdue",G10:G83,"High") |
D6 | D6 | =COUNTIFS(F10:F83,"Overdue",G10:G83,"Medium") |
E6 | E6 | =COUNTIFS(F10:F83,"Overdue",G10:G83,"Low") |
G6 | G6 | =IFERROR(F6/F7,"0%") |
C7:F7 | C7 | =SUM(C2:C6) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
I10:I83 | Cell Value | ="Strategic Initiative" | text | NO |
I10:I83 | Cell Value | ="Abutter Activity" | text | NO |
I10:I83 | Cell Value | ="Major Projects" | text | NO |
H10:H83 | Cell Value | contains "BUMC" | text | NO |
H10:H83 | Cell Value | contains "Fenway" | text | NO |
H10:H83 | Cell Value | contains "CRC" | text | NO |
I10:I83 | Cell Value | contains "Community Relations" | text | NO |
I10:I83 | Cell Value | contains " " | text | NO |
I10:I83 | Cell Value | contains "Design" | text | NO |
I10:I83 | Cell Value | contains "Community Relations" | text | NO |
I10:I83 | Cell Value | contains "Construction" | text | NO |
I10:I83 | Cell Value | contains "Program" | text | NO |
E1 | Cell Value | contains "Low" | text | NO |
E1 | Cell Value | contains "Medium" | text | NO |
E1 | Cell Value | contains "High" | text | NO |
D1 | Cell Value | contains "Low" | text | NO |
D1 | Cell Value | contains "Medium" | text | NO |
D1 | Cell Value | contains "High" | text | NO |
C1 | Cell Value | contains "Low" | text | NO |
C1 | Cell Value | contains "Medium" | text | NO |
C1 | Cell Value | contains "High" | text | NO |
B2:B6 | Cell Value | contains "Overdue" | text | NO |
B2:B6 | Cell Value | contains "On Hold" | text | NO |
B2:B6 | Cell Value | contains "Complete" | text | NO |
B2:B6 | Cell Value | contains "In Progress" | text | NO |
B2:B6 | Cell Value | contains "Not Started" | text | NO |
G10:I83 | Cell Value | contains "Low" | text | NO |
G10:I83 | Cell Value | contains "Medium" | text | NO |
G10:I83 | Cell Value | contains "High" | text | NO |
F10:F83 | Cell Value | contains "Overdue" | text | NO |
F10:F83 | Cell Value | contains "On Hold" | text | NO |
F10:F83 | Cell Value | contains "Complete" | text | NO |
F10:F83 | Cell Value | contains "In Progress" | text | NO |
F10:F83 | Cell Value | contains "Not Started" | text | NO |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
F10:F83 | List | ='Drop Downs'!$A$3:$A$8 |
G10:G83 | List | ='Drop Downs'!$C$3:$C$6 |
H10:H83 | List | ='Drop Downs'!$E$3:$E$6 |
I10:I83 | List | ='Drop Downs'!$G$3:$G$6 |
Book1 | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | High | Medium | Low | STATUS TOTAL | % OF TOTAL | |||||||
2 | Not Started | 0 | 0 | 0 | 0 | 0% | ||||||
3 | In Progress | 0 | 1 | 0 | 1 | 100% | ||||||
4 | Complete | 0 | 0 | 0 | 0 | 0% | ||||||
5 | On Hold | 0 | 0 | 0 | 0 | 0% | ||||||
6 | Overdue | 0 | 0 | 0 | 0 | 0% | ||||||
7 | PRIORITY TOTAL | 0 | 1 | 0 | 1 | |||||||
8 | Complete table, below. Chart counts will populate automatically, above. | |||||||||||
9 | TASK ID | TASK | ASSIGNED TO | START DATE | END DATE | STATUS | PRIORITY LEVEL | CAMPUS | CATEGORY | COMMENTS | ||
10 | 2.1 | Example | Example | 01/01/2022 | In Progress | Medium | CRC | Strategic Initiative | ||||
11 | ||||||||||||
12 | ||||||||||||
13 | ||||||||||||
14 | ||||||||||||
15 | ||||||||||||
16 | ||||||||||||
17 | ||||||||||||
18 | ||||||||||||
19 | ||||||||||||
20 | ||||||||||||
21 | ||||||||||||
22 | ||||||||||||
23 | ||||||||||||
24 | ||||||||||||
25 | ||||||||||||
Rosenthal - 2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C2 | C2 | =COUNTIFS(F10:F83,"Not Started",G10:G83,"High") |
D2 | D2 | =COUNTIFS(F10:F83,"Not Started",G10:G83,"Medium") |
E2 | E2 | =COUNTIFS(F10:F83,"Not Started",G10:G83,"Low") |
F2:F6 | F2 | =SUM(C2:E2) |
G2 | G2 | =IFERROR(F2/F7,"0%") |
C3 | C3 | =COUNTIFS(F10:F83,"In Progress",G10:G83,"High") |
D3 | D3 | =COUNTIFS(F10:F83,"In Progress",G10:G83,"Medium") |
E3 | E3 | =COUNTIFS(F10:F83,"In Progress",G10:G83,"Low") |
G3 | G3 | =IFERROR(F3/F7,"0%") |
C4 | C4 | =COUNTIFS(F10:F83,"Complete",G10:G83,"High") |
D4 | D4 | =COUNTIFS(F10:F83,"Complete",G10:G83,"Medium") |
E4 | E4 | =COUNTIFS(F10:F83,"Complete",G10:G83,"Low") |
G4 | G4 | =IFERROR(F4/F7,"0%") |
C5 | C5 | =COUNTIFS(F10:F83,"On Hold",G10:G83,"High") |
D5 | D5 | =COUNTIFS(F10:F83,"On Hold",G10:G83,"Medium") |
E5 | E5 | =COUNTIFS(F10:F83,"On Hold",G10:G83,"Low") |
G5 | G5 | =IFERROR(F5/F7,"0%") |
C6 | C6 | =COUNTIFS(F10:F83,"Overdue",G10:G83,"High") |
D6 | D6 | =COUNTIFS(F10:F83,"Overdue",G10:G83,"Medium") |
E6 | E6 | =COUNTIFS(F10:F83,"Overdue",G10:G83,"Low") |
G6 | G6 | =IFERROR(F6/F7,"0%") |
C7:F7 | C7 | =SUM(C2:C6) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
I10:I83 | Cell Value | ="Strategic Initiative" | text | NO |
I10:I83 | Cell Value | ="Abutter Activity" | text | NO |
I10:I83 | Cell Value | ="Major Projects" | text | NO |
H10:H83 | Cell Value | contains "BUMC" | text | NO |
H10:H83 | Cell Value | contains "Fenway" | text | NO |
H10:H83 | Cell Value | contains "CRC" | text | NO |
I10:I83 | Cell Value | contains "Community Relations" | text | NO |
I10:I83 | Cell Value | contains " " | text | NO |
I10:I83 | Cell Value | contains "Design" | text | NO |
I10:I83 | Cell Value | contains "Community Relations" | text | NO |
I10:I83 | Cell Value | contains "Construction" | text | NO |
I10:I83 | Cell Value | contains "Program" | text | NO |
E1 | Cell Value | contains "Low" | text | NO |
E1 | Cell Value | contains "Medium" | text | NO |
E1 | Cell Value | contains "High" | text | NO |
D1 | Cell Value | contains "Low" | text | NO |
D1 | Cell Value | contains "Medium" | text | NO |
D1 | Cell Value | contains "High" | text | NO |
C1 | Cell Value | contains "Low" | text | NO |
C1 | Cell Value | contains "Medium" | text | NO |
C1 | Cell Value | contains "High" | text | NO |
B2:B6 | Cell Value | contains "Overdue" | text | NO |
B2:B6 | Cell Value | contains "On Hold" | text | NO |
B2:B6 | Cell Value | contains "Complete" | text | NO |
B2:B6 | Cell Value | contains "In Progress" | text | NO |
B2:B6 | Cell Value | contains "Not Started" | text | NO |
G10:I83 | Cell Value | contains "Low" | text | NO |
G10:I83 | Cell Value | contains "Medium" | text | NO |
G10:I83 | Cell Value | contains "High" | text | NO |
F10:F83 | Cell Value | contains "Overdue" | text | NO |
F10:F83 | Cell Value | contains "On Hold" | text | NO |
F10:F83 | Cell Value | contains "Complete" | text | NO |
F10:F83 | Cell Value | contains "In Progress" | text | NO |
F10:F83 | Cell Value | contains "Not Started" | text | NO |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
F10:F83 | List | ='Drop Downs'!$A$3:$A$8 |
G10:G83 | List | ='Drop Downs'!$C$3:$C$6 |
H10:H83 | List | ='Drop Downs'!$E$3:$E$6 |
I10:I83 | List | ='Drop Downs'!$G$3:$G$6 |