Matching values based on start and end point in a cell range

[FM1]

I've drawn up a fairly rudimentary project roadmap in excel, which includes project name, start date (quarter year) [column D], end date (quarter year) [column E] and then the timeline itself with green bars reflecting the duration of the project. Instead of simply colouring in the cells, I add a value of 's' and use conditional formatting to colour the cell. The formula I need is to match the start date (first S in cell range) and end date (last s in cell range) and enter that value into columns D and E. If it's easier, I can change the value for end of the cell range if it makes it easier, e.g. change the final S to an 'F' to indicate the finish date.

 

[jasonb75]

Bad layout is going to make it difficult at best. Due to the way that merged cells work, you will likely only get the correct result if the first or last 'S' is in January.

Please update your account details to show which version of excel you are using so that we can see which functions you have available before attempting to find something that may or may not work.