Project stage details by selecting a date

[chssundeep]

Hi Friends,

I need to thank you all the leaders in the forum for your continuous support to all the people whoever posts a question.

I am struggling a lot to get the required output... Please help me on this..

I have 2 projects with phases and milestones... If I select a DATE i need to get the Project wise and phase wise details...

In Sheet 1 I have the details of projects and Phases
In Sheet 2 I have the dates of each phase
In sheet 3 I have mentioned the output Im looking for

I have attached a sample file for the above mentioned problem...

 

[bosco_yip]

Try,

1] In F8, copied down :

=IFERROR(INDEX('Sheet 2'!D$7:AF$7,MATCH(D$5,INDEX('Sheet 2'!D$6:AF$83,MATCH(C$7&" "&C8,'Sheet 2'!B$6:B$83,0)+MATCH(D8,'Sheet 2'!B$7:B$12,0),0),0))&"/"&INDEX('Sheet 2'!C$6:C$83,MATCH(C$7&" "&C8,'Sheet 2'!B$6:B$83,0)+MATCH(D8,'Sheet 2'!B$7:B$12,0)),"Not Started")

2] In F18, copied down :

=IFERROR(INDEX('Sheet 2'!D$7:AF$7,MATCH(D$5,INDEX('Sheet 2'!D$6:AF$83,MATCH(C$17&" "&C18,'Sheet 2'!B$6:B$83,0)+MATCH(D18,'Sheet 2'!B$56:B$58,0),0),0))&"/"&INDEX('Sheet 2'!C$6:C$83,MATCH(C$17&" "&C18,'Sheet 2'!B$6:B$83,0)+MATCH(D18,'Sheet 2'!B$56:B$58,0)),"Not Started")

Regards
Bosco

 

[chssundeep]

Thank you very much Bosco_yip... if I change the date the phase, milestone should also be changed.. Attached a fresh file...Please look into it

 

[bosco_yip]

Thank you very much Bosco_yip... if I change the date the phase, milestone should also be changed.. Attached a fresh file...Please look into it

Then,

In G9, copied down :

=IFERROR(INDEX('Sheet 2'!D$7:AF$7,MATCH(D$5,INDEX('Sheet 2'!D$6:AF$83,MATCH(C9&" "&D9,'Sheet 2'!B$6:B$83,0)+MATCH(E9,IF(C9="Project SM",'Sheet 2'!B$7:B$12,'Sheet 2'!B$56:B$58),0),0),0))&"/"&INDEX('Sheet 2'!C$6:C$83,MATCH(C9&" "&D9,'Sheet 2'!B$6:B$83,0)+MATCH(E9,IF(C9="Project SM",'Sheet 2'!B$7:B$12,'Sheet 2'!B$56:B$58),0)),"Not Started")

Regards
Bosco

 

[chssundeep]

Thank you Bosco, I need all the details to be populated not only day..

I need Project Name, Phase, Milestone and Day should come automatically by selecting the date

 

[bosco_yip]

Thank you Bosco, I need all the details to be populated not only day..

I need Project Name, Phase, Milestone and Day should come automatically by selecting the date

Sorry misunderstanding the question, herein the fixed formulas in below :

1] "Project Name" C9, copied down :

=IFERROR("Project "&SUBSTITUTE(INDEX('Sheet 1'!$C$7:$C$8,SUMPRODUCT(ROW($A$1:$A$2)*('Sheet 1'!$E$7:$I$8=E9)))," ",""),"")

2] "Phase" D9, copied down :

=IFERROR("Phase "&LOOKUP(2,1/ISNUMBER(FIND(C9,'Sheet 2'!B$1:INDEX('Sheet 2'!B:B,AGGREGATE(15,6,ROW('Sheet 2'!$D$6:$AF$83)/('Sheet 2'!$D$6:$AF$83=$D$5),ROWS($1:1))))),'Sheet 2'!C$1:INDEX('Sheet 2'!C:C,AGGREGATE(15,6,ROW('Sheet 2'!$D$6:$AF$83)/('Sheet 2'!$D$6:$AF$83=$D$5),ROWS($1:1)))),"")

3] "Milestone" E9, copied down :

=IFERROR(INDEX('Sheet 2'!B:B,AGGREGATE(15,6,ROW('Sheet 2'!$D$6:$AF$83)/('Sheet 2'!$D$6:$AF$83=$D$5),ROWS($1:1))),"")

4] "Day" F9, copied down :

=IFERROR(MOD(AGGREGATE(15,6,ROW('Sheet 2'!$D$6:$AF$83)/1%+COLUMN('Sheet 2'!$A$1:$AC$1)/('Sheet 2'!$D$6:$AF$83=$D$5),ROWS($1:1)),100)&"/"&INDEX('Sheet 2'!C:C,AGGREGATE(15,6,ROW('Sheet 2'!$D$6:$AF$83)/('Sheet 2'!$D$6:$AF$83=$D$5),ROWS($1:1))),"")

5] Click D5 dropdown list, to select the date for checking

Regards
Bosco

 

[chssundeep]

Thanks a ton Bosco,

It was so kind of you for the effort...

 

[chssundeep]

Dear Bosco,

Can you please explain me the formula...

I am not able to understand why ROW($A$1:$A$2) is taken in the first formula

2. Aggregator

3. In 4th Formula you have taken 1%...

So can you please explain this

 

[bosco_yip]

Dear Bosco,

Can you please explain me the formula...

I am not able to understand why ROW($A$1:$A$2) is taken in the first formula

2. Aggregator

3. In 4th Formula you have taken 1%...

So can you please explain this

1] ROW($A$1:$A$2) in formula 1

ROW($A$1:$A$2) is equal to {1;2}, because you have only 2 rows of "Project Name" in sheet 1, C7 and C8

2] AGGREGATE function

Please press F1, to open the "Help" file and search for "AGGREGATE function" for further detailing

3] In 4th Formula you have taken 1%...

ROW('Sheet 2'!$D$6:$AF$83)/1% is equal to ROW('Sheet 2'!$D$6:$AF$83)*100

Regards
Bosco