dynamic project tracking with scroll bar

[barmacost]

Hoping someone has an easy solution but I've basically created a project gantt chart with each column representing one day. Above each month's dates I have a merged cell that displays the name of the month. For ease of reporting I've created a second tab that shows only 4 months worth of data using offset formulas with a scroll bar to scroll through the remainder of the year. Problem is I can't figure out how to scroll by monthly increments. If I scroll by day then because of the merged cells the months don't line up or display correctly. Do I need to change my offset formula somehow to move in monthly increments or do I need to adjust the properties of the scroll bar?


Thanks, Brian

 

[Hui]

Brian

Your scroll bar will be linked to a cell which will increment the months


On the Second Tab (with 4 months of data) you will setup day headers (say on the second row) which are linked to the Scroll Bar reference cell eg:

=Date(2010,Ref Cell,1)

and then every adjacenet cell will be incremented by 1 to the right

This row will have a Custom Number format of d or dd


On the Top Row where you want your month labels I would put something like

B1: =If(Day(B2)=14,B2,"")

and setup the number format to Custom and enter MMM YY or whatever you want


This way the days will be consistant 1-31 etc and the Month Titles will only be above the 14th of the Month (roughly in the middle of each month)

 

[barmacost]

Thanks Hui. Because I have the column widths so narrow I had to merge every 2 cells in the top row so the month name was visible but your formula did the trick with the addition of "OR" formula. Thanks a bunch.