Hi again, I really can't figure this one out either.
I have a data set (C8:K522).
Each row represents one record.
Each row is sequentially numbered 1 - 514
Column D is "Relates To"
Column G is "Start Date", and
Column H is "Due Date".
Example of what I would like to achieve...
There is Data in row 17 (H17) that has a due date of 1 January, 2016.
A user adds new data into row 25, but types 17 into D25.
I would like a UserForm to pop up when somebody enters a value into a cell in column D that asks the user how many days to add to the due date. It would then take the date value from H17, add the number of days as per the UserForm, and populate G25.
Similarly, this would work in the same way if the user typed a number from any row that contained text (IE not empty)..
Again, I hope this makes sense, and I hope that someone can help.
I have done a sceeen shot of the sheet below if that helps.
Cheers
WT
I have a data set (C8:K522).
Each row represents one record.
Each row is sequentially numbered 1 - 514
Column D is "Relates To"
Column G is "Start Date", and
Column H is "Due Date".
Example of what I would like to achieve...
There is Data in row 17 (H17) that has a due date of 1 January, 2016.
A user adds new data into row 25, but types 17 into D25.
I would like a UserForm to pop up when somebody enters a value into a cell in column D that asks the user how many days to add to the due date. It would then take the date value from H17, add the number of days as per the UserForm, and populate G25.
Similarly, this would work in the same way if the user typed a number from any row that contained text (IE not empty)..
Again, I hope this makes sense, and I hope that someone can help.
I have done a sceeen shot of the sheet below if that helps.
Excel 2010 | |||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | |||
1 | #N/A | ||||||||||||||||||
2 | Insert Project Name Here | Line Colour | |||||||||||||||||
3 | Select a name from above then Click HERE to email a PDF or click HERE to view a PDF | ||||||||||||||||||
4 | |||||||||||||||||||
5 | Project Manager: | ||||||||||||||||||
6 | Project Coordinator: | ||||||||||||||||||
7 | |||||||||||||||||||
8 | LINE COLOUR | FOLLOW AFTER LINE # | LINE NUMBER | DELIVERABLES | OWNER | START DATE | DUE DATE | COMPLETED DATE | STATUS | DAYS | New Start Date | ||||||||
9 | 1 | Coffee Machines organised for Delivery to Amberley | AH | Tue 5 Apr | Tue 5 Apr | 98 Days Overdue | 1 | ||||||||||||
10 | 2 | Finalise Menu Options | AH | Wed 23 Mar | Wed 6 Apr | 97 Days Overdue | 14 | ||||||||||||
11 | 3 | Discuss with Army setup resources (stocking and setup of container) | AH | Wed 23 Mar | Wed 6 Apr | 97 Days Overdue | 14 | ||||||||||||
12 | 2 | 4 | Meet with 2FSB to discuss planning for planning and Hamil | AH | Wed 6 Apr | Wed 6 Apr | 97 Days Overdue | 1 | 24/03/2016 | ||||||||||
13 | 5 | Get Microwave Tagged and Tested | AH | Wed 23 Mar | Thu 7 Apr | 96 Days Overdue | 15 | ||||||||||||
Data |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B1 | =VLOOKUP(F1,List!$A$2:$C$35,3,FALSE) | |
B5 | ="Project Manager: " | |
B6 | ="Project Coordinator: " | |
J9 | =IFERROR(IF(OR(G9="",H9=""),"",IF(I9<>"","Complete",IF($A$2-H9=0,"Due Today",IF(H9>$A$2,"",IF($A$2-H9 = 1,$A$2-H9&" Day Overdue",IF($A$2-H9 > 1,$A$2-H9&" Days Overdue")))))),"") | |
J10 | =IFERROR(IF(OR(G10="",H10=""),"",IF(I10<>"","Complete",IF($A$2-H10=0,"Due Today",IF(H10>$A$2,"",IF($A$2-H10 = 1,$A$2-H10&" Day Overdue",IF($A$2-H10 > 1,$A$2-H10&" Days Overdue")))))),"") | |
J11 | =IFERROR(IF(OR(G11="",H11=""),"",IF(I11<>"","Complete",IF($A$2-H11=0,"Due Today",IF(H11>$A$2,"",IF($A$2-H11 = 1,$A$2-H11&" Day Overdue",IF($A$2-H11 > 1,$A$2-H11&" Days Overdue")))))),"") | |
J12 | =IFERROR(IF(OR(G12="",H12=""),"",IF(I12<>"","Complete",IF($A$2-H12=0,"Due Today",IF(H12>$A$2,"",IF($A$2-H12 = 1,$A$2-H12&" Day Overdue",IF($A$2-H12 > 1,$A$2-H12&" Days Overdue")))))),"") | |
J13 | =IFERROR(IF(OR(G13="",H13=""),"",IF(I13<>"","Complete",IF($A$2-H13=0,"Due Today",IF(H13>$A$2,"",IF($A$2-H13 = 1,$A$2-H13&" Day Overdue",IF($A$2-H13 > 1,$A$2-H13&" Days Overdue")))))),"") | |
K9 | =IFERROR(IF(AND(G9="",H9=""),"",IF(H9-G9=0,"1",H9-G9)),"") | |
K10 | =IFERROR(IF(AND(G10="",H10=""),"",IF(H10-G10=0,"1",H10-G10)),"") | |
K11 | =IFERROR(IF(AND(G11="",H11=""),"",IF(H11-G11=0,"1",H11-G11)),"") | |
K12 | =IFERROR(IF(AND(G12="",H12=""),"",IF(H12-G12=0,"1",H12-G12)),"") | |
K13 | =IFERROR(IF(AND(G13="",H13=""),"",IF(H13-G13=0,"1",H13-G13)),"") | |
D9 | =IF(E9="","","1") | |
D10 | =IF(E10="","",D9+1) | |
D11 | =IF(E11="","",D10+1) | |
D12 | =IF(E12="","",D11+1) | |
D13 | =IF(E13="","",D12+1) | |
R9 | =IFERROR(VLOOKUP(C9,$D$8:$G$522,4,FALSE)+1,"") | |
R10 | =IFERROR(VLOOKUP(C10,$D$8:$G$522,4,FALSE)+1,"") | |
R11 | =IFERROR(VLOOKUP(C11,$D$8:$G$522,4,FALSE)+1,"") | |
R12 | =IFERROR(VLOOKUP(C12,$D$8:$G$522,4,FALSE)+1,"") | |
R13 | =IFERROR(VLOOKUP(C13,$D$8:$G$522,4,FALSE)+1,"") |
Cheers
WT