Convert a vertical task list into a timeline (horizontal)

daand

New Member
Joined
Apr 14, 2016
Messages
3
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!!
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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....:biggrin:

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
HIJKLMN
3Weeks54321Project End
4 Point 1Point 3Point 1cPoint 2b
5Point 1aPoint 2
6Point 1bPoint 2a
Sheet1
Cell Formulas
RangeFormula
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.
 
Upvote 0
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;

Array Formulas
CellFormula
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))))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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?
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

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