Code/Function to Look at Previous Entry to Validate Data
 

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.

Code/Function to Look at Previous Entry to Validate Data
 

Try this...

Assume A1:D1 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 tabData Tools groupData ValidationAllowCustom

Formula:

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

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

When you're done OK out

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
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.

Code/Function to Look at Previous Entry to Validate Data
 

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

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


"T. Valko" wrote:

Try this...

Assume A1:D1 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 tabData Tools groupData ValidationAllowCustom

Formula:

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

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

When you're done OK out

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
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.

Please follow following steps
1.Click toolmacro from the menubar
2.Create a macro with any name (click create button on the macro dialog 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 the 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) < lastmilage 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 milage column
Note that the first row should not contain any numeric values related to the vehicle milage

Hope you get it

Have a nice time….


Chris
------
Convert your Excel spreadsheet into an online calculator.
http://www.spreadsheetconverter.com

Code/Function to Look at Previous Entry to Validate Data
 

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

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$2:D2)))
C3 = AND(COUNT(C3),C3=MAX(IF(B$2:B3=B3,D$2:D3)))
C4 = AND(COUNT(C4),C4=MAX(IF(B$2:B4=B4,D$2:D4)))
C5 = AND(COUNT(C5),C5=MAX(IF(B$2:B5=B5,D$2:D5)))

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$2:D2))) = TRUE so 10 in C2 is accepted.

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

MAX(IF(B$2:B2=B2,D$2:D2)) = 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$2:D3))) = 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$2:D3)) = 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.

--
Biff
Microsoft Excel MVP


"Khoshravan" wrote in message
...
Very nice solution. Could you please explain the logic behind your
formula?

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


"T. Valko" wrote:

Try this...

Assume A1:D1 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 tabData Tools groupData ValidationAllowCustom

Formula:

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

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

When you're done OK out

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
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.

Code/Function to Look at Previous Entry to Validate Data
 

Thanks for the solution, it worked perfect!

"T. Valko" wrote:

Try this...

Assume A1:D1 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 tabData Tools groupData ValidationAllowCustom

Formula:

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

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

When you're done OK out

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
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.

Code/Function to Look at Previous Entry to Validate Data
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
Thanks for the solution, it worked perfect!

"T. Valko" wrote:

Try this...

Assume A1:D1 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 tabData Tools groupData ValidationAllowCustom

Formula:

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

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

When you're done OK out

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
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.