I was provided an Excel template to create a timeline for an education project. I’m told the template was initially produced by a project engineer – he created it in some other software, and then exported / or re-created it in Excel. (Unfortunately, he is not around to consult with.) The spreadsheet uses drop-down lists in the Start and End columns to select the relevant weeks in the schedule, and colored bars that move as the week range is selected. (see attached)

My Excel skills are rather basic, and I don’t know what functions were used to create this timeline. Help menus were not very helpful since I couldn’t seem to describe the issue in short form. Here it is in long form:

I added additional rows of info and additional weeks in the timeline. The functions or formulas now need adjusting in order to work smoothly. I was able to copy some formulas to re-create the drop-down lists in new rows. I can also copy colored bars into new rows, but they don’t always move properly when I select a range of weeks in the drop-down menu.

Here is what I need in the end:

1. The list in the drop-down menus should include the full range of weeks in Row 2 – which is 57 weeks. As it stands, the list goes to 41 wks in most cases and only 26 in others.

2. The colored bars in each row should adjust smoothly to coincide with the weeks selected in the drop-down list. Theoretically, each bar should be able to extend from week 1 to week 57. Does this mean manually adjusting formulas that I don’t understand, or is it a function I select? And how do I insert a colored bar and appropriate formula in a new row?

3. If possible, I would like to change the color of the bars in the timeline.

If someone could point me in the right direction, I would be most grateful.