Excel 2007

Hi Rajeev,

Yes we resolved the issue using the concatenate function. I was trying to change the formatting of a cell with a student's name in it depending on whether they were ahead of / behind / on target based on the adjacent cell.

In order to provide a three result solution we created a new cell 'adding' the variance (to target) to the student's name using the concatenate function:

=CONCATENATE(B2," ",C2)

We then used traditional conditional formatting on this cell to create a 'traffic light' style format (actually formatting the cells rather than using the traffic light icons) by setting up three separate rules based on the "format only cells that contain..." and using "specific text" to format the cell depending on the results.

Of course this then leaves you with the variance showing next to the student's name, which is not ideal, but it gets the job done.

Since then I have decided to amend my layout - I am now using traditional traffic light icons in the actual variance cells, and for the student names I am now only highlighting the students who are behind target based on term targets i.e. their 'total to date' is behind where they should have been at the end of the last term.

By only needing one formatting style I am able to "Use a formula to determine which cells to format" - the formula I have used is:

=IF(AND($B$1>39437,E5<H5),1,IF(AND($B$1>39542,E5<(H5+K5)),1,IF(AND($B$1>39633,E5<D5),1,0)))

where I am using the TODAY function (in cell B1) to calculate which target to assess them against (whether they need to be measured against the year-to-date target for the Autumn, Spring or Summer term).

Thanks for your help after the Advanced session - it was extremely useful and has helped me solve my problem.

Regards,

Martin