Convert a vertical task list into a timeline (horizontal)

[daand]

Hi All,

I don't know if this is easily do-able or not:

I have a vertical list of tasks with sub tasks that have to be completed at a certain time before the project end date.
To make it easy, I would like to convert these items to a horizontal timeline which displays the tasks and sub tasks on a timeline depending how many weeks before the project end date..

I've put the data and sample I would like in the link below:
https://www.dropbox.com/s/5gw1zle09rbeeu4/Template Excel to check calender.xlsx?dl=0

Thanks and cheers!!

 

[RasGhul]

Hi Daand,

If a formula version is ok maybe this, just drag this formula across and down as many total rows you think you will need;

This also assumes that you don't have a main topic & sub topic on the same row....

I would adjust the column ranges to maybe 500-1000 rows past the lastrow of your data otherwise we could look at doing a table version to make it dynamic as you add more rows.

Book1
	H	I	J	K	L	M	N
3	Weeks	5	4	3	2	1	Project End
4			Point 1	Point 3	Point 1c	Point 2b
5			Point 1a		Point 2
6			Point 1b		Point 2a
Sheet1

Cell Formulas
Range	Formula
I4	{=IF(ROWS($I$4:I4)>COUNTIFS($E$11:$E$18,I$3),"",INDEX($B$11:$B$18&$C$11:$C$18,SMALL(IF($E$11:$E$18=I$3,ROW($B$11:$B$18)-ROW($B$11)+1),ROWS($I$4:I4))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[daand]

Hi Daand,

If a formula version is ok maybe this, just drag this formula across and down as many total rows you think you will need;

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I4[/TH]
[TD="align: left"]{=IF(ROWS($I$4:I4)>COUNTIFS($E$11:$E$18,I$3),"",INDEX($B$11:$B$18&$C$11:$C$18,SMALL(IF($E$11:$E$18=I$3,ROW($B$11:$B$18)-ROW($B$11)+1),ROWS($I$4:I4))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

That's amazing and works!
Now for the next request (sorry, it's a lot, I'm sorry), is there any way to link the output in the calender to the original content.
I've moved the calender to a seperate sheet and since it's a lot of content, I'm hoping to be able to hyperlink the calender items to their original locations.. Would that be possible too?

 

[RasGhul]

I'm guessing where you have actually moved your data to, but it's possible. This setup may run slow and a smaller search area to hyperlink to maybe better if you have problems.

Here is a link for my mock-up as reference so you can change the ranges to suit your data;


https://www.dropbox.com/s/a96v4ukkggc4e0f/Template Excel to check calender_Daand.xlsx?dl=0