Progress the shape

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.
  • 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.
Sheet "Data" will be as below:
Sample-Progress.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
1ProjectMainActivityStartDateEndDateDaysSubActivity-01Start DateEnd DateDaysSubActivity-02Start DateEnd DateDaysSubActivity-03Start DateEnd DateDaysSubActivity-04Start DateEnd DateDaysSubActivity-05Start DateEnd DateDays
2Project-01Job 0107-Jan-202210-Jan-20223Job 01-A11-Jan-202215-Jan-20224Job 01-B16-Jan-202219-Jan-20223Job 01-C20-Jan-202222-Jan-20222Job 01-D23-Jan-202225-Jan-20222Job 01-E26-Jan-202227-Jan-20221
3Project-01Job 0203-Jan-202205-Jan-20222Job 02-A06-Jan-202208-Jan-20222Job 02-B09-Jan-202212-Jan-20223
4Project-01Job 0305-Jan-202210-Jan-20225
5Project-01Job 0407-Jan-202212-Jan-20225
6Project-01Job 0521-Jan-202223-Jan-20222Job 04-A24-Jan-202226-Jan-20222Job 04-B27-Jan-202230-Jan-20223Job 04-C31-Jan-202202-Feb-20222
7Project-02Job 0107-Jan-202210-Jan-20223Job 01-A11-Jan-202215-Jan-20224Job 01-B16-Jan-202219-Jan-20223Job 01-C20-Jan-202222-Jan-20222Job 01-D23-Jan-202225-Jan-20222Job 01-E26-Jan-202227-Jan-20221
8Project-02Job 0203-Jan-202205-Jan-20222Job 02-A06-Jan-202208-Jan-20222Job 02-B09-Jan-202212-Jan-20223
9Project-02Job 0305-Jan-202210-Jan-20225
10Project-02Job 0407-Jan-202212-Jan-20225
11Project-02Job 0521-Jan-202223-Jan-20222Job 04-A24-Jan-202226-Jan-20222Job 04-B27-Jan-202230-Jan-20223Job 04-C31-Jan-202202-Feb-20222
12Project-03Job 0107-Jan-202210-Jan-20223Job 01-A11-Jan-202215-Jan-20224Job 01-B16-Jan-202219-Jan-20223Job 01-C20-Jan-202222-Jan-20222Job 01-D23-Jan-202225-Jan-20222Job 01-E26-Jan-202227-Jan-20221
13Project-03Job 0203-Jan-202205-Jan-20222Job 02-A06-Jan-202208-Jan-20222Job 02-B09-Jan-202212-Jan-20223
14Project-03Job 0305-Jan-202210-Jan-20225
15Project-03Job 0407-Jan-202212-Jan-20225
16Project-03Job 0521-Jan-202223-Jan-20222Job 04-A24-Jan-202226-Jan-20222Job 04-B27-Jan-202230-Jan-20223Job 04-C31-Jan-202202-Feb-20222
Data
Cell Formulas
RangeFormula
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,U2Q2=P2-O2



Sheet "Progress Shape" update as attached:

Please help me to get desired output.

Thanks & Regards,
Shahid
 

Attachments

  • Sample-Output.jpg
    Sample-Output.jpg
    170.2 KB · Views: 6

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

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