Code/Function to Look at Previous Entry to Validate Data

[Steve]

I've been looking all over the web for an answer or example of a code that I
can use in a vehicle mileage log.

I have 4 columns:

1. Date
2. Vehicle
3. Start Mileage
4. Ending Mileage

In the vehicle column, I have 3 vehicles (Vehicle 1, Vehicle 2, and Vehicle
3).
When entering an entry into the log, I enter the date, the Vehicle #, then
the start mile (at this point I would like Excel to look back through the
Vehicle column and find the last entry for the particular vehicle # I just
entered and then look at the ending mileage to ensure that the starting
mileage for the new entry is equal to or greater than the ending mileage for
that vehicle number.

Example

ROW DATE VEHICLE START MILEAGE END MILEAGE
1 1/1/2009 Vehicle # 1 10,005 10, 250
2 1/2/2009 Vehicle # 2 100,000 100,025
3 1/2/2009 Vehicle # 3 50,000 50,317
4 1/3/2009 Vehicle #2

Using the example above, when entering row 4, I would like to look back
through the previous entries in the Vehicle Column until finds the last entry
for a particular vehicle (in this case Vehicle #2) then limit my enter into
the Start Mileage Column to a value equal to or greater than the previous
ending mileage (in this case, my value must be equal to or greater than
100,025). If I enter starting mileage that is less than the previous ending
mileage for that vehicle, I would like to recieve an error message

I'm using Excel 2007.

Your assistance is greatly appreciated.

 

[T. Valko]

Try this...

Assume A11 are your column headers
Select the range C2:Cn

Where n = a number of rows that is sufficient to allow for future data
entry. C2:C100? C2:C500?

Goto Data tab>Data Tools group>Data Validation>Allow>Custom

Formula:

=AND(COUNT(C2),C2>=MAX(IF(B$2:B2=B2,D$22)))

You can enter a custom error message by clicking the Error Alert tab and
filling in the info.

When you're done OK out

 

[Khoshravan]

Very nice solution. Could you please explain the logic behind your formula?

R. Khoshravan
Please click "Yes" if it is helpful.

 

[Chris Bode]

Please follow following steps
1.Click tool>macro from the menubar
2.Create a macro with any name (click create button on the macro dialo
box with a particular name)
3.From the project explorer in VB IDE, double click Sheet1
4.From the code window select Worksheet from the drop down list at th
left
And paste following codes
#
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 3 Then
Dim row As Integer

row = 1
Dim lastmilage As Double

lastmilage = 0
While row < Target.row - 1

If (Sheet1.Cells(row, 2).Value = Sheet1.Cells(Target.row
1, 2).Value) Then
lastmilage = CDbl(Sheet1.Cells(row, 3).Value)
End If

row = row + 1
Wend
If Target.row > 1 Then

If CDbl(Sheet1.Cells(Target.row - 1, 3).Value) < lastmilag
Then

MsgBox "Wrong entry"

Sheet1.Cells(Target.row - 1, 3).Select

End If
End If
End If
End Sub
#
5.Now run the macro and check by entering less value at the from milag
column
Note that the first row should not contain any numeric values relate
to the vehicle milage

Hope you get it

Have a nice time….


Chris

 

[T. Valko]

=AND(COUNT(C2),C2>=MAX(IF(B$2:B2=B2,D$22)))

Let's assume the validation range is C2:C5.

The formula references will automatically increment for each cell that it is
applied to:

C2 = AND(COUNT(C2),C2>=MAX(IF(B$2:B2=B2,D$22)))
C3 = AND(COUNT(C3),C3>=MAX(IF(B$2:B3=B3,D$23)))
C4 = AND(COUNT(C4),C4>=MAX(IF(B$2:B4=B4,D$24)))
C5 = AND(COUNT(C5),C5>=MAX(IF(B$2:B5=B5,D$25)))

Logically, we can assume that the end mileage must be greater than the
starting mileage and the next starting mileage must be greater than or equal
to the last ending mileage. Also, each successive ending mileage must be >
than the previous ending mileage. As the user enters the data one row at a
time the formula checks for the maximum end mileage for the vehicle number
entered on that row. It compares that number against the starting mileage
entered on that row. If the new starting mileage is not >= the max ending
mileage for that vehicle number then the formula evaluates to FALSE and
causes the error message to display.

...........B...........C.........D
1...Vehicle.....Start.....End
2........1..........10.............

AND(COUNT(C2),C2>=MAX(IF(B$2:B2=B2,D$22))) = TRUE so 10 in C2 is accepted.

COUNT(C2) = 1
The entry in C2 is a number

MAX(IF(B$2:B2=B2,D$22)) = 0
The max end mileage for Veh1 is an empty cell which evaluates as 0.

C2>=0 = TRUE
10>=0 = TRUE

=AND(1,TRUE) = TRUE
10 is an accepted entry

...........B...........C.........D
1...Vehicle.....Start.....End
2........1..........10........15
3........1..........12...........

AND(COUNT(C3),C3>=MAX(IF(B$2:B3=B3,D$23))) = FALSE so 12 in C3 is not
accepted.

COUNT(C3) = 1
The entry in C3 is a number

MAX(IF(B$2:B3=B3,D$23)) = 15
The max end mileage for Veh1 is 15

C3>=15 = FALSE
12>=15 = FALSE

=AND(1,FALSE) = FALSE
12 is not an accepted entry so the error message is displayed.

As new data is entered on each row this is how it is evaluated.

 

[Steve]

Thanks for the solution, it worked perfect!

Try this...

Assume A11 are your column headers
Select the range C2:Cn

Where n = a number of rows that is sufficient to allow for future data
entry. C2:C100? C2:C500?

Goto Data tab>Data Tools group>Data Validation>Allow>Custom

Formula:

=AND(COUNT(C2),C2>=MAX(IF(B$2:B2=B2,D$22)))

You can enter a custom error message by clicking the Error Alert tab and
filling in the info.

When you're done OK out

 

[T. Valko]

You're welcome. Thanks for the feedback!