shahidsamikhan
New Member
- Joined
- Dec 4, 2014
- Messages
- 17
Dear All,
I have project wise activity data in one sheet and based on entry of data I want to change the other sheet's shape, if we add new record or change the record then it should create the shape or change the shape accordingly;
This is only a sample; in reality, I have a long list of activities, each with its own set of sub-activities.
Sheet "Progress Shape" update as attached:
Please help me to get desired output.
Thanks & Regards,
Shahid
I have project wise activity data in one sheet and based on entry of data I want to change the other sheet's shape, if we add new record or change the record then it should create the shape or change the shape accordingly;
This is only a sample; in reality, I have a long list of activities, each with its own set of sub-activities.
- If I change the "Start Date" value, the shape should begin on that date in the same row cell and continue until the End Date with the same color shape as the activity color.
- If some activity has some sub-activity then shape should be in same row as enter start and end date.
Sample-Progress.xlsx | |||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | |||
1 | Project | MainActivity | StartDate | EndDate | Days | SubActivity-01 | Start Date | End Date | Days | SubActivity-02 | Start Date | End Date | Days | SubActivity-03 | Start Date | End Date | Days | SubActivity-04 | Start Date | End Date | Days | SubActivity-05 | Start Date | End Date | Days | ||
2 | Project-01 | Job 01 | 07-Jan-2022 | 10-Jan-2022 | 3 | Job 01-A | 11-Jan-2022 | 15-Jan-2022 | 4 | Job 01-B | 16-Jan-2022 | 19-Jan-2022 | 3 | Job 01-C | 20-Jan-2022 | 22-Jan-2022 | 2 | Job 01-D | 23-Jan-2022 | 25-Jan-2022 | 2 | Job 01-E | 26-Jan-2022 | 27-Jan-2022 | 1 | ||
3 | Project-01 | Job 02 | 03-Jan-2022 | 05-Jan-2022 | 2 | Job 02-A | 06-Jan-2022 | 08-Jan-2022 | 2 | Job 02-B | 09-Jan-2022 | 12-Jan-2022 | 3 | ||||||||||||||
4 | Project-01 | Job 03 | 05-Jan-2022 | 10-Jan-2022 | 5 | ||||||||||||||||||||||
5 | Project-01 | Job 04 | 07-Jan-2022 | 12-Jan-2022 | 5 | ||||||||||||||||||||||
6 | Project-01 | Job 05 | 21-Jan-2022 | 23-Jan-2022 | 2 | Job 04-A | 24-Jan-2022 | 26-Jan-2022 | 2 | Job 04-B | 27-Jan-2022 | 30-Jan-2022 | 3 | Job 04-C | 31-Jan-2022 | 02-Feb-2022 | 2 | ||||||||||
7 | Project-02 | Job 01 | 07-Jan-2022 | 10-Jan-2022 | 3 | Job 01-A | 11-Jan-2022 | 15-Jan-2022 | 4 | Job 01-B | 16-Jan-2022 | 19-Jan-2022 | 3 | Job 01-C | 20-Jan-2022 | 22-Jan-2022 | 2 | Job 01-D | 23-Jan-2022 | 25-Jan-2022 | 2 | Job 01-E | 26-Jan-2022 | 27-Jan-2022 | 1 | ||
8 | Project-02 | Job 02 | 03-Jan-2022 | 05-Jan-2022 | 2 | Job 02-A | 06-Jan-2022 | 08-Jan-2022 | 2 | Job 02-B | 09-Jan-2022 | 12-Jan-2022 | 3 | ||||||||||||||
9 | Project-02 | Job 03 | 05-Jan-2022 | 10-Jan-2022 | 5 | ||||||||||||||||||||||
10 | Project-02 | Job 04 | 07-Jan-2022 | 12-Jan-2022 | 5 | ||||||||||||||||||||||
11 | Project-02 | Job 05 | 21-Jan-2022 | 23-Jan-2022 | 2 | Job 04-A | 24-Jan-2022 | 26-Jan-2022 | 2 | Job 04-B | 27-Jan-2022 | 30-Jan-2022 | 3 | Job 04-C | 31-Jan-2022 | 02-Feb-2022 | 2 | ||||||||||
12 | Project-03 | Job 01 | 07-Jan-2022 | 10-Jan-2022 | 3 | Job 01-A | 11-Jan-2022 | 15-Jan-2022 | 4 | Job 01-B | 16-Jan-2022 | 19-Jan-2022 | 3 | Job 01-C | 20-Jan-2022 | 22-Jan-2022 | 2 | Job 01-D | 23-Jan-2022 | 25-Jan-2022 | 2 | Job 01-E | 26-Jan-2022 | 27-Jan-2022 | 1 | ||
13 | Project-03 | Job 02 | 03-Jan-2022 | 05-Jan-2022 | 2 | Job 02-A | 06-Jan-2022 | 08-Jan-2022 | 2 | Job 02-B | 09-Jan-2022 | 12-Jan-2022 | 3 | ||||||||||||||
14 | Project-03 | Job 03 | 05-Jan-2022 | 10-Jan-2022 | 5 | ||||||||||||||||||||||
15 | Project-03 | Job 04 | 07-Jan-2022 | 12-Jan-2022 | 5 | ||||||||||||||||||||||
16 | Project-03 | Job 05 | 21-Jan-2022 | 23-Jan-2022 | 2 | Job 04-A | 24-Jan-2022 | 26-Jan-2022 | 2 | Job 04-B | 27-Jan-2022 | 30-Jan-2022 | 3 | Job 04-C | 31-Jan-2022 | 02-Feb-2022 | 2 | ||||||||||
Data |
Cell Formulas | ||
---|---|---|
Range | Formula | |
Q2,Q16,M16,I16,E2:E16,M11:M13,I11:I13,Y12,U12,Q11:Q12,M6:M8,I6:I8,Y7,U7,Q6:Q7,M2:M3,I2:I3,Y2,U2 | Q2 | =P2-O2 |
Sheet "Progress Shape" update as attached:
Please help me to get desired output.
Thanks & Regards,
Shahid