Copy data from one worksheet to another based on cell value

K

Kgreen214

New Member
Joined
Mar 25, 2022
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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.

Book1
ABCDEFGHIJ
1HighMediumLowSTATUS TOTAL% OF TOTAL
2Not Started00000%
3In Progress00000%
4Complete00000%
5On Hold00000%
6Overdue00000%
7PRIORITY TOTAL0000
8Complete table, below. Chart counts will populate automatically, above.
9TASK IDTASKASSIGNED TOSTART DATEEND DATESTATUSPRIORITY LEVELCAMPUSCATEGORYCOMMENTS
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Summary
Cell Formulas
RangeFormula
C2C2=COUNTIFS(F10:F83,"Not Started",G10:G83,"High")
D2D2=COUNTIFS(F10:F83,"Not Started",G10:G83,"Medium")
E2E2=COUNTIFS(F10:F83,"Not Started",G10:G83,"Low")
F2:F6F2=SUM(C2:E2)
G2G2=IFERROR(F2/F7,"0%")
C3C3=COUNTIFS(F10:F83,"In Progress",G10:G83,"High")
D3D3=COUNTIFS(F10:F83,"In Progress",G10:G83,"Medium")
E3E3=COUNTIFS(F10:F83,"In Progress",G10:G83,"Low")
G3G3=IFERROR(F3/F7,"0%")
C4C4=COUNTIFS(F10:F83,"Complete",G10:G83,"High")
D4D4=COUNTIFS(F10:F83,"Complete",G10:G83,"Medium")
E4E4=COUNTIFS(F10:F83,"Complete",G10:G83,"Low")
G4G4=IFERROR(F4/F7,"0%")
C5C5=COUNTIFS(F10:F83,"On Hold",G10:G83,"High")
D5D5=COUNTIFS(F10:F83,"On Hold",G10:G83,"Medium")
E5E5=COUNTIFS(F10:F83,"On Hold",G10:G83,"Low")
G5G5=IFERROR(F5/F7,"0%")
C6C6=COUNTIFS(F10:F83,"Overdue",G10:G83,"High")
D6D6=COUNTIFS(F10:F83,"Overdue",G10:G83,"Medium")
E6E6=COUNTIFS(F10:F83,"Overdue",G10:G83,"Low")
G6G6=IFERROR(F6/F7,"0%")
C7:F7C7=SUM(C2:C6)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I10:I83Cell Value="Strategic Initiative"textNO
I10:I83Cell Value="Abutter Activity"textNO
I10:I83Cell Value="Major Projects"textNO
H10:H83Cell Valuecontains "BUMC"textNO
H10:H83Cell Valuecontains "Fenway"textNO
H10:H83Cell Valuecontains "CRC"textNO
I10:I83Cell Valuecontains "Community Relations"textNO
I10:I83Cell Valuecontains " "textNO
I10:I83Cell Valuecontains "Design"textNO
I10:I83Cell Valuecontains "Community Relations"textNO
I10:I83Cell Valuecontains "Construction"textNO
I10:I83Cell Valuecontains "Program"textNO
E1Cell Valuecontains "Low"textNO
E1Cell Valuecontains "Medium"textNO
E1Cell Valuecontains "High"textNO
D1Cell Valuecontains "Low"textNO
D1Cell Valuecontains "Medium"textNO
D1Cell Valuecontains "High"textNO
C1Cell Valuecontains "Low"textNO
C1Cell Valuecontains "Medium"textNO
C1Cell Valuecontains "High"textNO
B2:B6Cell Valuecontains "Overdue"textNO
B2:B6Cell Valuecontains "On Hold"textNO
B2:B6Cell Valuecontains "Complete"textNO
B2:B6Cell Valuecontains "In Progress"textNO
B2:B6Cell Valuecontains "Not Started"textNO
G10:I83Cell Valuecontains "Low"textNO
G10:I83Cell Valuecontains "Medium"textNO
G10:I83Cell Valuecontains "High"textNO
F10:F83Cell Valuecontains "Overdue"textNO
F10:F83Cell Valuecontains "On Hold"textNO
F10:F83Cell Valuecontains "Complete"textNO
F10:F83Cell Valuecontains "In Progress"textNO
F10:F83Cell Valuecontains "Not Started"textNO
Cells with Data Validation
CellAllowCriteria
F10:F83List='Drop Downs'!$A$3:$A$8
G10:G83List='Drop Downs'!$C$3:$C$6
H10:H83List='Drop Downs'!$E$3:$E$6
I10:I83List='Drop Downs'!$G$3:$G$6


Book1
ABCDEFGHIJ
1HighMediumLowSTATUS TOTAL% OF TOTAL
2Not Started00000%
3In Progress0101100%
4Complete00000%
5On Hold00000%
6Overdue00000%
7PRIORITY TOTAL0101
8Complete table, below. Chart counts will populate automatically, above.
9TASK IDTASKASSIGNED TOSTART DATEEND DATESTATUSPRIORITY LEVELCAMPUSCATEGORYCOMMENTS
101.1ExampleExample01/01/2022In ProgressMediumCRCMajor Projects
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Data Sciences - 1
Cell Formulas
RangeFormula
C2C2=COUNTIFS(F10:F83,"Not Started",G10:G83,"High")
D2D2=COUNTIFS(F10:F83,"Not Started",G10:G83,"Medium")
E2E2=COUNTIFS(F10:F83,"Not Started",G10:G83,"Low")
F2:F6F2=SUM(C2:E2)
G2G2=IFERROR(F2/F7,"0%")
C3C3=COUNTIFS(F10:F83,"In Progress",G10:G83,"High")
D3D3=COUNTIFS(F10:F83,"In Progress",G10:G83,"Medium")
E3E3=COUNTIFS(F10:F83,"In Progress",G10:G83,"Low")
G3G3=IFERROR(F3/F7,"0%")
C4C4=COUNTIFS(F10:F83,"Complete",G10:G83,"High")
D4D4=COUNTIFS(F10:F83,"Complete",G10:G83,"Medium")
E4E4=COUNTIFS(F10:F83,"Complete",G10:G83,"Low")
G4G4=IFERROR(F4/F7,"0%")
C5C5=COUNTIFS(F10:F83,"On Hold",G10:G83,"High")
D5D5=COUNTIFS(F10:F83,"On Hold",G10:G83,"Medium")
E5E5=COUNTIFS(F10:F83,"On Hold",G10:G83,"Low")
G5G5=IFERROR(F5/F7,"0%")
C6C6=COUNTIFS(F10:F83,"Overdue",G10:G83,"High")
D6D6=COUNTIFS(F10:F83,"Overdue",G10:G83,"Medium")
E6E6=COUNTIFS(F10:F83,"Overdue",G10:G83,"Low")
G6G6=IFERROR(F6/F7,"0%")
C7:F7C7=SUM(C2:C6)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I10:I83Cell Value="Strategic Initiative"textNO
I10:I83Cell Value="Abutter Activity"textNO
I10:I83Cell Value="Major Projects"textNO
H10:H83Cell Valuecontains "BUMC"textNO
H10:H83Cell Valuecontains "Fenway"textNO
H10:H83Cell Valuecontains "CRC"textNO
I10:I83Cell Valuecontains "Community Relations"textNO
I10:I83Cell Valuecontains " "textNO
I10:I83Cell Valuecontains "Design"textNO
I10:I83Cell Valuecontains "Community Relations"textNO
I10:I83Cell Valuecontains "Construction"textNO
I10:I83Cell Valuecontains "Program"textNO
E1Cell Valuecontains "Low"textNO
E1Cell Valuecontains "Medium"textNO
E1Cell Valuecontains "High"textNO
D1Cell Valuecontains "Low"textNO
D1Cell Valuecontains "Medium"textNO
D1Cell Valuecontains "High"textNO
C1Cell Valuecontains "Low"textNO
C1Cell Valuecontains "Medium"textNO
C1Cell Valuecontains "High"textNO
B2:B6Cell Valuecontains "Overdue"textNO
B2:B6Cell Valuecontains "On Hold"textNO
B2:B6Cell Valuecontains "Complete"textNO
B2:B6Cell Valuecontains "In Progress"textNO
B2:B6Cell Valuecontains "Not Started"textNO
G10:I83Cell Valuecontains "Low"textNO
G10:I83Cell Valuecontains "Medium"textNO
G10:I83Cell Valuecontains "High"textNO
F10:F83Cell Valuecontains "Overdue"textNO
F10:F83Cell Valuecontains "On Hold"textNO
F10:F83Cell Valuecontains "Complete"textNO
F10:F83Cell Valuecontains "In Progress"textNO
F10:F83Cell Valuecontains "Not Started"textNO
Cells with Data Validation
CellAllowCriteria
F10:F83List='Drop Downs'!$A$3:$A$8
G10:G83List='Drop Downs'!$C$3:$C$6
H10:H83List='Drop Downs'!$E$3:$E$6
I10:I83List='Drop Downs'!$G$3:$G$6


Book1
ABCDEFGHIJ
1HighMediumLowSTATUS TOTAL% OF TOTAL
2Not Started00000%
3In Progress0101100%
4Complete00000%
5On Hold00000%
6Overdue00000%
7PRIORITY TOTAL0101
8Complete table, below. Chart counts will populate automatically, above.
9TASK IDTASKASSIGNED TOSTART DATEEND DATESTATUSPRIORITY LEVELCAMPUSCATEGORYCOMMENTS
102.1ExampleExample01/01/2022In ProgressMediumCRCStrategic Initiative
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Rosenthal - 2
Cell Formulas
RangeFormula
C2C2=COUNTIFS(F10:F83,"Not Started",G10:G83,"High")
D2D2=COUNTIFS(F10:F83,"Not Started",G10:G83,"Medium")
E2E2=COUNTIFS(F10:F83,"Not Started",G10:G83,"Low")
F2:F6F2=SUM(C2:E2)
G2G2=IFERROR(F2/F7,"0%")
C3C3=COUNTIFS(F10:F83,"In Progress",G10:G83,"High")
D3D3=COUNTIFS(F10:F83,"In Progress",G10:G83,"Medium")
E3E3=COUNTIFS(F10:F83,"In Progress",G10:G83,"Low")
G3G3=IFERROR(F3/F7,"0%")
C4C4=COUNTIFS(F10:F83,"Complete",G10:G83,"High")
D4D4=COUNTIFS(F10:F83,"Complete",G10:G83,"Medium")
E4E4=COUNTIFS(F10:F83,"Complete",G10:G83,"Low")
G4G4=IFERROR(F4/F7,"0%")
C5C5=COUNTIFS(F10:F83,"On Hold",G10:G83,"High")
D5D5=COUNTIFS(F10:F83,"On Hold",G10:G83,"Medium")
E5E5=COUNTIFS(F10:F83,"On Hold",G10:G83,"Low")
G5G5=IFERROR(F5/F7,"0%")
C6C6=COUNTIFS(F10:F83,"Overdue",G10:G83,"High")
D6D6=COUNTIFS(F10:F83,"Overdue",G10:G83,"Medium")
E6E6=COUNTIFS(F10:F83,"Overdue",G10:G83,"Low")
G6G6=IFERROR(F6/F7,"0%")
C7:F7C7=SUM(C2:C6)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I10:I83Cell Value="Strategic Initiative"textNO
I10:I83Cell Value="Abutter Activity"textNO
I10:I83Cell Value="Major Projects"textNO
H10:H83Cell Valuecontains "BUMC"textNO
H10:H83Cell Valuecontains "Fenway"textNO
H10:H83Cell Valuecontains "CRC"textNO
I10:I83Cell Valuecontains "Community Relations"textNO
I10:I83Cell Valuecontains " "textNO
I10:I83Cell Valuecontains "Design"textNO
I10:I83Cell Valuecontains "Community Relations"textNO
I10:I83Cell Valuecontains "Construction"textNO
I10:I83Cell Valuecontains "Program"textNO
E1Cell Valuecontains "Low"textNO
E1Cell Valuecontains "Medium"textNO
E1Cell Valuecontains "High"textNO
D1Cell Valuecontains "Low"textNO
D1Cell Valuecontains "Medium"textNO
D1Cell Valuecontains "High"textNO
C1Cell Valuecontains "Low"textNO
C1Cell Valuecontains "Medium"textNO
C1Cell Valuecontains "High"textNO
B2:B6Cell Valuecontains "Overdue"textNO
B2:B6Cell Valuecontains "On Hold"textNO
B2:B6Cell Valuecontains "Complete"textNO
B2:B6Cell Valuecontains "In Progress"textNO
B2:B6Cell Valuecontains "Not Started"textNO
G10:I83Cell Valuecontains "Low"textNO
G10:I83Cell Valuecontains "Medium"textNO
G10:I83Cell Valuecontains "High"textNO
F10:F83Cell Valuecontains "Overdue"textNO
F10:F83Cell Valuecontains "On Hold"textNO
F10:F83Cell Valuecontains "Complete"textNO
F10:F83Cell Valuecontains "In Progress"textNO
F10:F83Cell Valuecontains "Not Started"textNO
Cells with Data Validation
CellAllowCriteria
F10:F83List='Drop Downs'!$A$3:$A$8
G10:G83List='Drop Downs'!$C$3:$C$6
H10:H83List='Drop Downs'!$E$3:$E$6
I10:I83List='Drop Downs'!$G$3:$G$6
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
You must log in or register to reply here.

Similar threads

K
  • Question
Modifying Existing VBA Code to add 2 additional data columns
Replies
0
Views
136
keranali
K
W
  • Solved
Removing Data Validation List Items Once Selected In Another Cell
Replies
3
Views
393
James006
James006
K
  • Question
Automatically move rows to another sheet and back based on value
2 3
Replies
22
Views
2K
mumps
mumps
A
  • Question
How to Automatically Insert Rows based on Count Values
Replies
2
Views
437
andyka
A
J
  • Question
Loop through Data Validation list in Advanced Filter Copy Paste all Results on one sheet
Replies
1
Views
1K
jsepanski
J

Forum statistics

Threads
1,214,614
Messages
6,120,530
Members
448,969
Latest member
mirek8991

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top