1

I have a vehicle data sheet with 3 different columns (L7:N56) to track the odometer reading during services or at the beginning/end of a trip.

  1. I want to be able to check all three columns (L7:N56) and highlight the first instance that is greater than or equal (>=) to 3000 miles greater than the last oil change odometer in cell M4 as a warning that we will need an oil change soon (M4+3000).
  2. I also want to be able to highlight the first instance that is greater than or equal (>=) to 5000 miles greater than the last oil change odometer in M4 (M4+5000).

I do not want to highlight every value that meets the criteria (no duplicates), only the first instances.

example of vehicle data spreadsheet

1
  • While an image may give some indication of intention, it doesn't provide an actual way for the volunteer contributors here to access data, develop a solution, test outcomes or leave working formulas. I suggest that you share a link to a copy of this spreadsheet.
    – Erik Tyler
    Jul 26, 2022 at 23:59

1 Answer 1

0

cond form

If you want such a result in the range L5:N56, just use,for RED, the custom formula:

=L5:N5=query(index(sort(unique(flatten($L$5:$N$56)),1,1)),"select Col1 where Col1 >="&$M$2+5000&"Limit 1",0)

and, for YELLOW, the formula:

=L5:N5=query(index(sort(unique(flatten($L$5:$N$56)),1,1)),"select Col1 where Col1 >="&$M$2+3000&"Limit 1",0)

Link to test file

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.