SereneSea
New Member
- Joined
- Feb 2, 2022
- Messages
- 43
- Office Version
- 2016
- Platform
- Windows
Hello all, I am so new to this VBA but I hope you can help.
I have a shared training sheet where other users can enter their training status and its represented in a gantt chart. I would like a code that:
I have a shared training sheet where other users can enter their training status and its represented in a gantt chart. I would like a code that:
- If a person adds a row at the bottom of a table and writes a Trainer name in columne E, ie Beta, it automatically moves the whole row to where the rest of the Betas are in.
- My B column is used as a data label for the chart and to make the chart look nice it needs to be merged, otherwise it looks repetitive if a code can do this as well. Picture below as an example what I like to see and not see.
- And if possible, each time someone inserts a row in the last column to have all the formulas automatically get added (ie column C concatenate)
Example1.xlsm | ||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | |||
1 | Training Plan 2021 | DO not delete Columns K to O please | ||||||||||||||||||
2 | ||||||||||||||||||||
3 | Plan Start | 4/1/2021 | ||||||||||||||||||
4 | Trainer for chart | Concate for chart | Test | Trainer | Trainee | PRIORITY | START | PLAN END | ACTUAL END | % COMPLETE | Project Start | Days to Start | Complete | Incomplete | Plan Days | |||||
5 | Alpha | Test 1 (T: Alpha) | Test 1 | Alpha | Hotel | HIGH | 7/1/2021 | 8/1/2021 | 7/9/2021 | 100% | 4/1/2021 | 91 | 31 | 0 | 31 | |||||
6 | Test 2 (T: Alpha) | Test 2 | Alpha | Delta | HIGH | 6/1/2021 | 7/1/2021 | 100% | 4/1/2021 | 61 | 30 | 0 | 30 | |||||||
7 | Test 3 (T: Alpha) | Test 3 | Alpha | Juliett | HIGH | 4/1/2021 | 5/5/2021 | 4/13/2021 | 100% | 4/1/2021 | 0 | 34 | 0 | 34 | ||||||
8 | Test 4 (T: Alpha) | Test 4 | Alpha | Charlie | HIGH | 6/1/2021 | 7/1/2021 | 6/10/2021 | 100% | 4/1/2021 | 61 | 30 | 0 | 30 | ||||||
9 | Test 5 (T: Alpha) | Test 5 | Alpha | Golf | HIGH | 5/1/2021 | 6/1/2021 | 7/7/2021 | 100% | 4/1/2021 | 30 | 31 | 0 | 31 | ||||||
10 | Beta | Test 6 (T: Beta) | Test 6 | Beta | Alpha | LOW | 7/1/2021 | 6/30/2022 | 25% | 4/1/2021 | 91 | 91 | 273 | 364 | ||||||
11 | Test 7 (T: Beta) | Test 7 | Beta | Echo | HIGH | 7/1/2021 | 3/31/2022 | 75% | 4/1/2021 | 91 | 204.75 | 68.25 | 273 | |||||||
12 | Test 8 (T: Beta) | Test 8 | Beta | Delta | LOW | 3/1/2022 | 12/1/2021 | 0% | 4/1/2021 | 334 | 0 | -90 | -90 | |||||||
13 | Test 9 (T: Beta) | Test 9 | Beta | Juliett | LOW | 9/1/2021 | 12/1/2021 | 0% | 4/1/2021 | 153 | 0 | 91 | 91 | |||||||
14 | Test 10 (T: Beta) | Test 10 | Beta | Charlie | LOW | 9/1/2021 | 10/1/2021 | 0% | 4/1/2021 | 153 | 0 | 30 | 30 | |||||||
15 | Test 11 (T: Beta) | Test 11 | Beta | Lima | LOW | 9/1/2021 | 10/1/2021 | 0% | 4/1/2021 | 153 | 0 | 30 | 30 | |||||||
16 | Charlie | Test 12 (T: Charlie) | Test 12 | Charlie | Romeo | Medium - refresher | 5/15/2021 | 10/31/2021 | 75% | 4/1/2021 | 44 | 126.75 | 42.25 | 169 | ||||||
17 | Charlie | Test 13 (T: Charlie) | Test 13 | Charlie | Aplha | Low | 9/1/2021 | 10/1/2021 | 0% | 4/1/2021 | 153 | 0 | 30 | 30 | ||||||
18 | Delta | Test 14 (T: Delta) | Test 14 | Delta | Hotel | Medium - refresher | 5/15/2021 | 5/30/2021 | 5/25/2021 | 100% | 4/1/2021 | 44 | 15 | 0 | 15 | |||||
19 | Delta | Test 15 (T: Delta) | Test 15 | Delta | Delta | Medium - refresher | 5/15/2021 | 5/30/2021 | 5/25/2021 | 100% | 4/1/2021 | 44 | 15 | 0 | 15 | |||||
20 | Delta | Test 16 (T: Delta) | Test 16 | Delta | Juliett | HIGH | 5/1/2021 | 6/1/2021 | 100% | 4/1/2021 | 30 | 31 | 0 | 31 | ||||||
21 | Delta | Test 17 (T: Delta) | Test 17 | Delta | Charlie | HIGH | 5/1/2021 | 6/1/2021 | 100% | 4/1/2021 | 30 | 31 | 0 | 31 | ||||||
22 | Echo | Test 18 (T: Echo) | Test 18 | Echo | Golf | High | 6/1/2021 | 10/31/2021 | 100% | 4/1/2021 | 61 | 152 | 0 | 152 | ||||||
23 | Echo | Test 19 (T: Echo) | Test 19 | Echo | Zulu | Low | 9/1/2021 | 11/1/2021 | 50% | 4/1/2021 | 153 | 30.5 | 30.5 | 61 | ||||||
24 | Echo | Test 20 (T: Echo) | Test 20 | Echo | Yankee | MEDIUM | 1/1/2022 | 3/31/2022 | 50% | 4/1/2021 | 275 | 44.5 | 44.5 | 89 | ||||||
25 | Echo | Test 21 (T: Echo) | Test 21 | Echo | Whiskey | Medium | 6/1/2021 | 7/1/2021 | 100% | 4/1/2021 | 61 | 30 | 0 | 30 | ||||||
26 | Echo | Test 22 (T: Echo) | Test 22 | Echo | Apha | Low | 11/1/2021 | 12/1/2021 | 0% | 4/1/2021 | 214 | 0 | 30 | 30 | ||||||
27 | Echo | Test 23 (T: Echo) | Test 23 | Echo | Delta | Medium | 10/31/2021 | 11/30/2021 | 50% | 4/1/2021 | 213 | 15 | 15 | 30 | ||||||
28 | Foxtrot | Test 24 (T: Foxtrot) | Test 24 | Foxtrot | Delta | Low | 9/1/2021 | 10/1/2021 | 25% | 4/1/2021 | 153 | 7.5 | 22.5 | 30 | ||||||
29 | Foxtrot | Test 25 (T: Foxtrot) | Test 25 | Foxtrot | Juliett | High - need to understand calculation and macro | 5/1/2021 | 8/1/2021 | 25% | 44317 | 23 | 69 | 92 | |||||||
30 | Foxtrot | Test 26 (T: Foxtrot) | Test 26 | Foxtrot | Lima | Medium - refresher | 6/1/2021 | 7/1/2021 | 100% | 4/1/2021 | 61 | 30 | 0 | 30 | ||||||
31 | Golf | Test 27 (T: Golf) | Test 27 | Golf | Beta | HIGH | 8/1/2021 | 12/31/2021 | 50% | 4/1/2021 | 122 | 76 | 76 | 152 | ||||||
32 | Golf | Test 28 (T: Golf) | Test 28 | Golf | Charlie | LOW | 6/1/2021 | 8/1/2021 | 0% | 4/1/2021 | 61 | 0 | 61 | 61 | ||||||
33 | Beta | Test 29 (T: Beta) | Test 29 | Beta | Alpha | HIGH | 7/1/2021 | 3/31/2022 | 75% | 44378 | 204.75 | 68.25 | 273 | |||||||
34 | ||||||||||||||||||||
Training Plan |
Cell Formulas | ||
---|---|---|
Range | Formula | |
M5:M33 | M5 | =IF(ISBLANK(H5),0,H5-L5) |
N5:N33 | N5 | =$K5*$P5 |
O5:O33 | O5 | =P5-N5 |
P5:P33 | P5 | =IF(ISBLANK(I5),0,I5-H5) |
B5,B10,B16:B33 | B5 | =Table2[@Trainer] |
C5:C33 | C5 | =CONCAT(Table2[@Test]," (T: ",Table2[@Trainer],")") |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
K22:K28 | Other Type | DataBar | NO | |
G22:G28 | Cell Value | contains "LOW" | text | NO |
G22:G28 | Cell Value | contains "MEDIUM" | text | NO |
G22:G28 | Cell Value | contains "HIGH" | text | NO |
K29:K33 | Other Type | DataBar | NO | |
G29:G33 | Cell Value | contains "LOW" | text | NO |
G29:G33 | Cell Value | contains "MEDIUM" | text | NO |
G29:G33 | Cell Value | contains "HIGH" | text | NO |
K18:K21 | Other Type | DataBar | NO | |
G18:G21 | Cell Value | contains "LOW" | text | NO |
G18:G21 | Cell Value | contains "MEDIUM" | text | NO |
G18:G21 | Cell Value | contains "HIGH" | text | NO |
K16:K17 | Other Type | DataBar | NO | |
G16:G17 | Cell Value | contains "LOW" | text | NO |
G16:G17 | Cell Value | contains "MEDIUM" | text | NO |
G16:G17 | Cell Value | contains "HIGH" | text | NO |
K10:K15 | Other Type | DataBar | NO | |
G10:G15 | Cell Value | contains "LOW" | text | NO |
G10:G15 | Cell Value | contains "MEDIUM" | text | NO |
G10:G15 | Cell Value | contains "HIGH" | text | NO |
K5:K9 | Other Type | DataBar | NO | |
G5:G9 | Cell Value | contains "LOW" | text | NO |
G5:G9 | Cell Value | contains "MEDIUM" | text | NO |
G5:G9 | Cell Value | contains "HIGH" | text | NO |
K34:K35 | Other Type | DataBar | NO | |
G34:G35 | Cell Value | contains "LOW" | text | NO |
G34:G35 | Cell Value | contains "MEDIUM" | text | NO |
G34:G35 | Cell Value | contains "HIGH" | text | NO |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
G5:G34 | List | HIGH,MEDIUM,LOW |
K5:K21 | List | =$C$49:$C$53 |
K22:K33 | List | =$C$48:$C$52 |
K34:K35 | List | =$D$48:$D$52 |
Last edited: