We track fuel usage using a fuel meter (much like an odometer) and use a cell data validation equation to prevent erroneous numbers from being input – a form of data quality control. If the meter reading is not the same or higher than the previous day or days, then a Stop Error pops up. It has been working fine.
Now that the meter has rolled over (max is 999,999), the data validation won’t allow the user to enter the number. We do not want to simply add 1,000,000 to the numbers to keep it going, because differential math calculations are also being conducted on the data in a different area of the spreadsheet.
Is there a way to modify this equation to account for the occasional (every two to three years) roll-overs, yet have the equation generic enough to simply copy across all worksheets?
Data is arranged by month on separate worksheets. On each worksheet, the daily meter is input horizontally from left to right on the same row. Column C is Day 1, D is Day 2 etc.
DV:O18
Use Excel 2010 on Win 7.
Example Table layout
<tbody>
</tbody>
Now that the meter has rolled over (max is 999,999), the data validation won’t allow the user to enter the number. We do not want to simply add 1,000,000 to the numbers to keep it going, because differential math calculations are also being conducted on the data in a different area of the spreadsheet.
Is there a way to modify this equation to account for the occasional (every two to three years) roll-overs, yet have the equation generic enough to simply copy across all worksheets?
Data is arranged by month on separate worksheets. On each worksheet, the daily meter is input horizontally from left to right on the same row. Column C is Day 1, D is Day 2 etc.
DV:O18
Code:
[FONT=Calibri][COLOR=#000000]=IF(COUNTIF($B18:N$18,">0")<1,O18>=MAX(INDIRECT("'"&TEXT($B$3-1,"mmm")&TEXT($B$3-1,"yy")&"'!C18:AG18")),IF(OR(ISBLANK(O18),ISTEXT(O18)),TRUE,AND(ISNUMBER(O18),O18>=LARGE(N18:$C18,1))))[/COLOR][/FONT]Use Excel 2010 on Win 7.
Example Table layout
| Col B | Col C | Col D | Col E | Col F | |
| Daily Fuel Meter Reading | 1 | 2 | 3 | 4 | 5 |
| Storage Tank #1 | 997735 | 999700 | 2340 | 4788 | |
<tbody>
</tbody>
