Create a report with multiple project data

[Swathi Ranjith]

Hi Everyone,

I am new to the forum so please bear with me while i try to explain my issue.

I need to create a log to record data from multiple projects in one place with similar stages/phases. Please see the sample document attached.

My issue is how to create a reporting structure with this data. I would like to see how each project is doing in terms of the overall timeframe and what phase/step of the project it is in. Also, need to generate a visual report on the overall progress status for all projects.

In the sample file, i included only 3 projects, however, there can be more projects added and i need to include all those details in the reporting.

I am open to restructuring my log sheet if there is another easy way to collate this information.

Would appreciate any help!

Regards,
Swathi

[Glenn Kennedy]

I think you need to manually add your expected output. we can then see how to get Excel to deliver it.

[Swathi Ranjith]

Hi Glenn,

Thanks for your response. I included another tab 'Expected Report Structure'. I would be delighted if i could start with this structure atleast.

Swathi

[Glenn Kennedy]

One slight change of layout. new column a on your raw data.

project name, copied down
=INDEX(RawData!$1:$1,,3+4*(ROWS($1:1)-1))&""

Phase & step, copied across 1 and down:
=IF(A2="","",LOOKUP("Zzzzz",OFFSET(RawData!$C$3:$C$25,,2+4*(COUNTA($A$2:$A2)-1),,),RawData!A$3:A$25))

Status:
=IF($A2="","",LOOKUP("Zzzzz",OFFSET(RawData!$C$3:$C$25,,2+4*(COUNTA($A$2:$A2)-1),,)))

I have no idea where the numbers for duration came from. Please explain!!

[Swathi Ranjith]

This is Amazing!!!!!!

Really appreciate all your help!

I roughly calculated the difference between the project start date to today's date. The purpose for that column is to know the time it has taken so far since the project started.


Swathi

[Glenn Kennedy]

Ok. Im out for an hour so so. Look back later.

[Glenn Kennedy]

The missing formula:

=IF($A2="","",TODAY()-OFFSET(RawData!$C$3,,4*(COUNTA($A$2:$A2)-1),,))

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.