Hi Experts
I have a project project sprint report that I need to send out. I usually insert rows manually.
Is there a way to automate the system.
What I want:
I have RAW DATA on another sheet that Contains Sprint number, Task, Start and Finish Date
I have 5 sprint 1 and 6 sprint 3. I want automatically insert 3 more rows for sprint 3 and two more rows for sprint 1
and I want to copy all the data over via matching columns header.
Is it possible? VBA or formula both solutions are welcome.
I have a project project sprint report that I need to send out. I usually insert rows manually.
Is there a way to automate the system.
What I want:
I have RAW DATA on another sheet that Contains Sprint number, Task, Start and Finish Date
I have 5 sprint 1 and 6 sprint 3. I want automatically insert 3 more rows for sprint 3 and two more rows for sprint 1
and I want to copy all the data over via matching columns header.
Is it possible? VBA or formula both solutions are welcome.
IC-Sprint-Planning-11596.xlsx | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | |||
4 | SPRINT 1 | 5 | 0% | SCOPE STATEMENT | |||||||||||
5 | SPRINT 2 | 4 | |||||||||||||
6 | SPRINT 3 | 6 | At Risk? | TASK NAME | FEATURE TYPE | RESPONSIBLE | STORY POINTS | START DATE | END DATE | DURATION in days | STATUS | PRIORITY | COMMENTS | ||
7 | SPRINT 4 | 4 | SPRINT 1 | ||||||||||||
8 | SPRINT 1,Task 1 | SPRINT 1 | Task 1 | ||||||||||||
9 | SPRINT 1,Task 2 | SPRINT 1 | Task 2 | ||||||||||||
10 | SPRINT 1,Task 3 | SPRINT 1 | Task 3 | ||||||||||||
11 | ,SPRINT 2 | SPRINT 2 | |||||||||||||
12 | SPRINT 2,Task 1 | SPRINT 2 | Task 1 | ||||||||||||
13 | SPRINT 2,Task 2 | SPRINT 2 | Task 2 | ||||||||||||
14 | SPRINT 2,Task 3 | SPRINT 2 | Task 3 | ||||||||||||
15 | ,SPRINT 3 | SPRINT 3 | |||||||||||||
16 | SPRINT 3,Task 1 | SPRINT 3 | Task 1 | ||||||||||||
17 | SPRINT 3,Task 2 | SPRINT 3 | Task 2 | ||||||||||||
18 | SPRINT 3,Task 3 | SPRINT 3 | Task 3 | ||||||||||||
19 | ,SPRINT 4 | SPRINT 4 | |||||||||||||
20 | SPRINT 4,Task 1 | SPRINT 4 | Task 1 | ||||||||||||
21 | SPRINT 4,Task 2 | SPRINT 4 | Task 2 | ||||||||||||
22 | SPRINT 4,Task 3 | SPRINT 4 | Task 3 | ||||||||||||
23 | ,SPRINT 5 | SPRINT 5 | |||||||||||||
24 | SPRINT 5,Task 1 | SPRINT 5 | Task 1 | ||||||||||||
25 | SPRINT 5,Task 2 | SPRINT 5 | Task 2 | ||||||||||||
26 | SPRINT 5,Task 3 | SPRINT 5 | Task 3 | ||||||||||||
BLANK - Sprint Planning |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B4:B7 | B4 | =COUNTIF('RAW DATA'!$A$3:$A$21,A4) |
A8:A26 | A8 | =B8&","&D8 |
J7:J26 | J7 | =IF(H7=0,"",I7-H7+1) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
C7:C26 | Cell Value | contains "No" | text | NO |
C7:C26 | Cell Value | contains "Yes" | text | NO |
L7:L26 | Cell Value | contains "Low" | text | NO |
L7:L26 | Cell Value | contains "Medium" | text | NO |
L7:L26 | Cell Value | contains "High" | text | NO |
K7:K26 | Cell Value | contains "Approved" | text | NO |
K7:K26 | Cell Value | contains "Needs Review" | text | NO |
K7:K26 | Cell Value | contains "Not Started" | text | NO |
K7:K26 | Cell Value | contains "On Hold" | text | NO |
K7:K26 | Cell Value | contains "Overdue" | text | NO |
K7:K26 | Cell Value | contains "Complete" | text | NO |
K7:K26 | Cell Value | contains "In Progress" | text | NO |
L7:L26 | Cell Value | contains "Approved" | text | NO |
L7:L26 | Cell Value | contains "Needs Review" | text | NO |
L7:L26 | Cell Value | contains "Not Started" | text | NO |
L7:L26 | Cell Value | contains "On Hold" | text | NO |
L7:L26 | Cell Value | contains "Overdue" | text | NO |
L7:L26 | Cell Value | contains "Complete" | text | NO |
L7:L26 | Cell Value | contains "In Progress" | text | NO |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
C7:C26 | List | ='Dropdown Keys - Do Not Delete -'!$B$3:$B$4 |
K7:K26 | List | ='Dropdown Keys - Do Not Delete -'!$F$3:$F$11 |
L7:L26 | List | ='Dropdown Keys - Do Not Delete -'!$D$3:$D$5 |
IC-Sprint-Planning-11596.xlsx | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
2 | SPRINT | Task | Start | Finish | ||
3 | Sprint 1 | Task 1 | 16/01/23 | 23/01/23 | ||
4 | Sprint 1 | Task 2 | 24/01/23 | 31/01/23 | ||
5 | Sprint 1 | Task 3 | 01/02/23 | 08/02/23 | ||
6 | Sprint 1 | Task 4 | 09/02/23 | 16/02/23 | ||
7 | Sprint 1 | Task 5 | 17/02/23 | 24/02/23 | ||
8 | Sprint 2 | Task 6 | 09/02/23 | 16/02/23 | ||
9 | Sprint 2 | Task 7 | 17/02/23 | 24/02/23 | ||
10 | Sprint 2 | Task 8 | 25/02/23 | 04/03/23 | ||
11 | Sprint 2 | Task 9 | 05/03/23 | 12/03/23 | ||
12 | Sprint 3 | Task 10 | 13/03/23 | 20/03/23 | ||
13 | Sprint 3 | Task 11 | 21/03/23 | 28/03/23 | ||
14 | Sprint 3 | Task 12 | 29/03/23 | 05/04/23 | ||
15 | Sprint 3 | Task 13 | 06/04/23 | 13/04/23 | ||
16 | Sprint 3 | Task 14 | 14/04/23 | 21/04/23 | ||
17 | Sprint 3 | Task 15 | 22/04/23 | 29/04/23 | ||
18 | Sprint 4 | Task 16 | 14/04/23 | 21/04/23 | ||
19 | Sprint 4 | Task 17 | 22/04/23 | 29/04/23 | ||
20 | Sprint 4 | Task 18 | 30/04/23 | 07/05/23 | ||
21 | Sprint 4 | Task 19 | 08/05/23 | 15/05/23 | ||
RAW DATA |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D3:D21 | D3 | =C3+7 |
C4:C7,C9:C17,C19:C21 | C4 | =D3+1 |
C8,C18 | C8 | =D5+1 |