Excel 2007 : Conditional formatting - multiple sheets

I have attached a very simplified version of a problem I'm trying to solve.

In the XC Timing sheet, I have a list of times that competitors took to complete a course - each time corrosponds to the competitor number on the same line. There is also a cell with the Minimum Time for completing the course.

In the Results sheet, I have a column for time penalties which are arrived at with a very involved formula.

What I am trying to do is put a conditional formatting colour on any cell in the results column, where the penalties were due to the competitor going too fast and having a time less than the minimum time.

It is a little more complicated than it may at first seem as competitors can also pick up penalties by going slower than a maximum time. I am only interested here in those who went too fast.

I can post the full worksheet if necessary. Thanks.

Hello,

as far as I can see, there is not enough data in the sheet to solve the issue.

What I am trying to do is put a conditional formatting colour on any cell in the results column, where the penalties were due to the competitor going too fast and having a time less than the minimum time.

Where is the data that helps determine what the penalties were for? Unless this data is accessible, it will not be possible to base a CF on that fact.

As a general approach for creating conditional formatting formulas: write the formula into a cell, so that the cell shows TRUE or FALSE. Copy down to see the effect on the other rows in the table. Once you have this working as desired, copy the formula to the conditional formatting formula box.

OK, point taken - I didn't post the full worksheet as I thought it was overkill. I have removed identifying information and posted it this time.

The relevant sheet names are the same as above. I got this worksheet designed and working as I wanted, then I discovered that it is necessary to indicate whether the penalties in Results Column F are due to going slower than the Optimum Time, or to going faster than the Too Fast time (as specified in the XC Timing sheet). It is going faster than the Too Fast time that needs to be indicated, preferably with an asterisk next to the penalties figure in Results Column F, but a slightly shaded cell background would do.

Otherwise a competitor could get 5 penalty points, and they wouldn't know whether they went 5 seconds too fast or 13 seconds too slow.

I hope I have made this clear? Thanks

You are using the following formula to arrive at the value in column F of the results sheet:

=IF('Score Sheet'!Y6="","",IF(COUNTIF('Score Sheet'!Y6,"E"),"E",('Score Sheet'!Y6)))

Use a variation of that formula for your conditional formatting formula, e.g.

=IF('Score Sheet'!Y6="",FALSE,IF(COUNTIF('Score Sheet'!Y6,"E"),FALSE,TRUE))

Then, the conditional formatting will kick in if the penalty points from Score Sheet!Y6 are returned.

Will this do the trick for you?