HELP - Trying to build a Roadmap in Excel

[PaulFerris]

Hi all,

New to the forum and a novice-to-medium user of excel. I have been asked by my boss to create a Roadmap in excel and I need help!

There are two sheets - 'Roadmap' (to display the pretty formatted table) and 'Data' (to hold the exported data)

The way I want the end result to look like on the 'Roadmap' sheet something like this http://i.imgur.com/XE8bRub.png (although if there was someway to automatically

The raw values on the 'Data' tab looks something like this (it is always sorted by Portfolio and then by Launch Week - but if it would work a better way happy to change it)

[TABLE="width: 380"]
<tbody>[TR]
[TD]Launch Week[/TD]
[TD]Portfolio[/TD]
[TD]Project Name[/TD]
[/TR]
[TR]
[TD]20-Feb-17[/TD]
[TD]Apps[/TD]
[TD]Item 1[/TD]
[/TR]
[TR]
[TD]27-Feb-17[/TD]
[TD]Apps[/TD]
[TD]Item 2[/TD]
[/TR]
[TR]
[TD]27-Feb-17[/TD]
[TD]Apps[/TD]
[TD]Item 3[/TD]
[/TR]
[TR]
[TD]6-Mar-17[/TD]
[TD]Apps[/TD]
[TD]Item 6[/TD]
[/TR]
[TR]
[TD]6-Mar-17[/TD]
[TD]Apps[/TD]
[TD]Item 7[/TD]
[/TR]
[TR]
[TD]6-Mar-17[/TD]
[TD]Apps[/TD]
[TD]Item 9[/TD]
[/TR]
[TR]
[TD]13-Mar-17[/TD]
[TD]Apps[/TD]
[TD]Item 5[/TD]
[/TR]
[TR]
[TD]13-Mar-17[/TD]
[TD]Billing & Credit[/TD]
[TD]Item 8[/TD]
[/TR]
[TR]
[TD]8-May-17[/TD]
[TD]Billing & Credit[/TD]
[TD]Item 10[/TD]
[/TR]
</tbody>[/TABLE]

So what I need to happen is cell C5 on the 'Roadmap' would go and query the 'Data" sheet and retrieve the first Project it finds that is happening in the Launch Week of C4 and has a Portfolio of "Apps".

C6 would then be the same, except I want it to ignore the returned result in C5
C7 would then be the same, except I want it to ignore the returned results in C5 and C6..

etc

I hope that makes sense.

I just dont know where to begin in how to write the formula to populate the cells on the 'Roadmap' sheet. And if I need a macro I dont even know how to create those suckers

If any kind soul out there could help I would be very grateful

Thanks

 

[PaulFerris]

Hi Eric,

Just a follow up to all of this...

If I wanted to have an item span multiple columns - say an event that was lasting longer than a 7 day block - is it possible to do this?

Obviously we would need an END_DATE column but how would we get the data to span multiple columns in the final formula