How do you set up autoformatting when the data you are basing it on is not in the cell you want to format i.e. you want to format a person's name based on the various data beside their name in a table?

Dear Matrin

Thank you for attending Excel 07 Inter and Adv course. I hope you enjoyed the course.


I believe that after the advanced course we sat down and resolved this query. I believe you were trying to apply the conditional formatting to one cell (traffic lights) depending on the other cell.

If it not much of an inconvenience to you can I please request you to post as a reply the steps we took to achieve that. I know we used the concatenate function and then in the conditional formatting we entered some formula.

Please post the answer at your earliest convenience so that other delegates who might be facing similar problem would be able to benefit from it.

I would also request you to mark this post as resolved.

Many thanks

Kindest Regards

Rajeev Rawat
MOS Master Instructor

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