How to Automatically Insert Rows based on Count Values

A

andyka

Board Regular
Joined
Sep 20, 2021
Messages
52
Office Version
  1. 2019
Platform
  1. Windows
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.

IC-Sprint-Planning-11596.xlsx
ABCDEFGHIJKLM
4SPRINT 150%SCOPE STATEMENT
5SPRINT 24
6SPRINT 36At Risk?TASK NAMEFEATURE TYPERESPONSIBLESTORY POINTSSTART DATEEND DATEDURATION in daysSTATUSPRIORITYCOMMENTS
7SPRINT 44SPRINT 1 
8SPRINT 1,Task 1SPRINT 1Task 1 
9SPRINT 1,Task 2SPRINT 1Task 2 
10SPRINT 1,Task 3SPRINT 1Task 3 
11,SPRINT 2SPRINT 2 
12SPRINT 2,Task 1SPRINT 2Task 1 
13SPRINT 2,Task 2SPRINT 2Task 2 
14SPRINT 2,Task 3SPRINT 2Task 3 
15,SPRINT 3SPRINT 3 
16SPRINT 3,Task 1SPRINT 3Task 1 
17SPRINT 3,Task 2SPRINT 3Task 2 
18SPRINT 3,Task 3SPRINT 3Task 3 
19,SPRINT 4SPRINT 4 
20SPRINT 4,Task 1SPRINT 4Task 1 
21SPRINT 4,Task 2SPRINT 4Task 2 
22SPRINT 4,Task 3SPRINT 4Task 3 
23,SPRINT 5SPRINT 5 
24SPRINT 5,Task 1SPRINT 5Task 1 
25SPRINT 5,Task 2SPRINT 5Task 2 
26SPRINT 5,Task 3SPRINT 5Task 3 
BLANK - Sprint Planning
Cell Formulas
RangeFormula
B4:B7B4=COUNTIF('RAW DATA'!$A$3:$A$21,A4)
A8:A26A8=B8&","&D8
J7:J26J7=IF(H7=0,"",I7-H7+1)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C7:C26Cell Valuecontains "No"textNO
C7:C26Cell Valuecontains "Yes"textNO
L7:L26Cell Valuecontains "Low"textNO
L7:L26Cell Valuecontains "Medium"textNO
L7:L26Cell Valuecontains "High"textNO
K7:K26Cell Valuecontains "Approved"textNO
K7:K26Cell Valuecontains "Needs Review"textNO
K7:K26Cell Valuecontains "Not Started"textNO
K7:K26Cell Valuecontains "On Hold"textNO
K7:K26Cell Valuecontains "Overdue"textNO
K7:K26Cell Valuecontains "Complete"textNO
K7:K26Cell Valuecontains "In Progress"textNO
L7:L26Cell Valuecontains "Approved"textNO
L7:L26Cell Valuecontains "Needs Review"textNO
L7:L26Cell Valuecontains "Not Started"textNO
L7:L26Cell Valuecontains "On Hold"textNO
L7:L26Cell Valuecontains "Overdue"textNO
L7:L26Cell Valuecontains "Complete"textNO
L7:L26Cell Valuecontains "In Progress"textNO
Cells with Data Validation
CellAllowCriteria
C7:C26List='Dropdown Keys - Do Not Delete -'!$B$3:$B$4
K7:K26List='Dropdown Keys - Do Not Delete -'!$F$3:$F$11
L7:L26List='Dropdown Keys - Do Not Delete -'!$D$3:$D$5



IC-Sprint-Planning-11596.xlsx
ABCD
2SPRINT TaskStartFinish
3Sprint 1Task 116/01/2323/01/23
4Sprint 1Task 224/01/2331/01/23
5Sprint 1Task 301/02/2308/02/23
6Sprint 1Task 409/02/2316/02/23
7Sprint 1Task 517/02/2324/02/23
8Sprint 2Task 609/02/2316/02/23
9Sprint 2Task 717/02/2324/02/23
10Sprint 2Task 825/02/2304/03/23
11Sprint 2Task 905/03/2312/03/23
12Sprint 3Task 1013/03/2320/03/23
13Sprint 3Task 1121/03/2328/03/23
14Sprint 3Task 1229/03/2305/04/23
15Sprint 3Task 1306/04/2313/04/23
16Sprint 3Task 1414/04/2321/04/23
17Sprint 3Task 1522/04/2329/04/23
18Sprint 4Task 1614/04/2321/04/23
19Sprint 4Task 1722/04/2329/04/23
20Sprint 4Task 1830/04/2307/05/23
21Sprint 4Task 1908/05/2315/05/23
RAW DATA
Cell Formulas
RangeFormula
D3:D21D3=C3+7
C4:C7,C9:C17,C19:C21C4=D3+1
C8,C18C8=D5+1
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
This is a sample of what I am looking for.
IC-Sprint-Planning-11596.xlsx
ABCDEFGHIJKLM
6SPRINT 36At Risk?TASK NAMEFEATURE TYPERESPONSIBLESTORY POINTSSTART DATEEND DATEDURATION in daysSTATUSPRIORITYCOMMENTS
7SPRINT 44SPRINT 1 
8SPRINT 1,Task 1SPRINT 1Task 1 
9SPRINT 1,Task 2SPRINT 1Task 2 
10SPRINT 1,Task 3SPRINT 1Task 3 
11Insert New ROWCopy Data from another sheetCopy dataCopy Data
12Insert New ROWCopy Data from another sheetCopy dataCopy Data
13,SPRINT 2SPRINT 2 
14SPRINT 2,Task 1SPRINT 2Task 1 
15SPRINT 2,Task 2SPRINT 2Task 2 
16SPRINT 2,Task 3SPRINT 2Task 3 
17,SPRINT 3SPRINT 3 
18SPRINT 3,Task 1SPRINT 3Task 1 
19SPRINT 3,Task 2SPRINT 3Task 2 
20SPRINT 3,Task 3SPRINT 3Task 3 
21Insert New ROWCopy Data from another sheetCopy dataCopy Data
22Insert New ROWCopy Data from another sheetCopy dataCopy Data
23Insert New ROWCopy Data from another sheetCopy dataCopy Data
BLANK - Sprint Planning
Cell Formulas
RangeFormula
B6:B7B6=COUNTIF('RAW DATA'!$A$3:$A$21,A6)
A8:A10,A13:A20A8=B8&","&D8
J7:J10,J13:J20J7=IF(H7=0,"",I7-H7+1)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C7:C31Cell Valuecontains "No"textNO
C7:C31Cell Valuecontains "Yes"textNO
L7:L31Cell Valuecontains "Low"textNO
L7:L31Cell Valuecontains "Medium"textNO
L7:L31Cell Valuecontains "High"textNO
K7:K31Cell Valuecontains "Approved"textNO
K7:K31Cell Valuecontains "Needs Review"textNO
K7:K31Cell Valuecontains "Not Started"textNO
K7:K31Cell Valuecontains "On Hold"textNO
K7:K31Cell Valuecontains "Overdue"textNO
K7:K31Cell Valuecontains "Complete"textNO
K7:K31Cell Valuecontains "In Progress"textNO
L7:L31Cell Valuecontains "Approved"textNO
L7:L31Cell Valuecontains "Needs Review"textNO
L7:L31Cell Valuecontains "Not Started"textNO
L7:L31Cell Valuecontains "On Hold"textNO
L7:L31Cell Valuecontains "Overdue"textNO
L7:L31Cell Valuecontains "Complete"textNO
L7:L31Cell Valuecontains "In Progress"textNO
Cells with Data Validation
CellAllowCriteria
C7:C23List='Dropdown Keys - Do Not Delete -'!$B$3:$B$4
K7:K31List='Dropdown Keys - Do Not Delete -'!$F$3:$F$11
L7:L31List='Dropdown Keys - Do Not Delete -'!$D$3:$D$5



Based on Count number in Range A4 : B7

IC-Sprint-Planning-11596.xlsx
AB
4SPRINT 15
5SPRINT 24
6SPRINT 36
7SPRINT 44
BLANK - Sprint Planning
Cell Formulas
RangeFormula
B4:B7B4=COUNTIF('RAW DATA'!$A$3:$A$21,A4)
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Pivot table not showing as Numbers
Replies
1
Views
274
shift-del
shift-del
M
  • Question Question
Counting specific dates from multiple columns
Replies
2
Views
358
mattbird
M
H
  • Solved
Need Formula Help
Replies
8
Views
361
Fluff
Fluff
F
  • Question Question
VBA Macro Issue: Replacing Simple Formula with Complex Formula Causes Error
Replies
2
Views
46
flashgordie
F
K
  • Question Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
600
awoohaw
awoohaw

Forum statistics

Threads
1,223,950
Messages
6,175,582
Members
452,653
Latest member
craigje92

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