Hi all I am new to this forum and hope it will be useful for you as well as for me!
My problem is that I have an excel spreadsheet that as each column heading has a work week date. ex. JAN 1, Jan 8, Jan 15… For the row headings lie the names of the employers. Now in the table are Project names assigned to a specific person that is then color coded to extend over the duration of the time allocated for a project. so if project 1 would take 3 weeks for Jim, then in Jim’s row would be a green bar that extended the length of 3 columns occupying 3 weeks. I hope this is clear.
What i need is to be able to count how many weeks each project will take. I can do that by using the countA and countblank functions if I assign each color bar to a range. I just add the 2 functions together to find out how many weeks a project is scheduled for. But I then need to transport the project duration to a worksheet that summarizes project length by quarters. The problem arises when a project starts in one quarter and ends in another. How can i link it to the other sheet so that it knows when 1 quarter ends and a another starts? also, if adjustments are made to the length of project 1 or project 16 is added all data must move down the right of the table to the next date. I dont know VBA so i was hoping this could be done using functions. If anyone has a better approach to this please let me know. Thanks in advance.
