Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    May 2002
    Posts
    36
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Code/Function to Look at Previous Entry to Validat (2007)

    I've been looking all over the web for an answer or example of a code or function 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, I must enter a value equal to or greater than 100,025). If I enter a less than the previous, ending mileage for that vehicle, I would like to recieve an error message.

    Your assistance is greatly appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code/Function to Look at Previous Entry to Validat (2007)

    Assuming that your data are in columns A, you could use Data | Validation with the custom option, starting from C3 down. The formula (with C3 as active cell) would be

    =C3>=MAX(IF($B$2:$B2=$B3,$D$2:$D2))

    See attached sample workbook.
    Attached Files Attached Files

  3. #3
    Lounger
    Join Date
    May 2002
    Posts
    36
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Code/Function to Look at Previous Entry to Validat (2007)

    Hans,
    Thank you. Once again you’ve come to my rescue!

  4. #4
    Lounger
    Join Date
    May 2002
    Posts
    36
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Hans,

    Back in early February I posted a message about Data | Validation with the custom option and you responded. Iím using your formula but for some reason Iím have some trouble.

    I entered the formula in cell G6 and then copy and pasted the validation w/formula to the remaining cells in Column G through row 200.

    For some reason the formula only appears to work for vehicle ďVentureĒ.

    Example: If you go to row 40 and select a vehicle other than Venture, you can enter any starting mileage itís accepted (e.g., F-150 beginning mileage = 45,000) the last ending mileage for F-150 (cell H33) was 45,521). If you now go to row 41 and attempt to enter a mileage record for Venture with a starting mileage that less than the last ending mileage you get the error message.

    I would greatly appreciate if you would take a look at the attached worksheet at your convenience and let me know what Iím doing wrong. Iím sure itís something simple. Again, thanks for your help.
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Validation appears to have become partially disabled. I could get it working again as follows:
    - Select G6:G200.
    - Open the Data Validation dialog.
    - Click OK (without changing anything).

  6. #6
    Lounger
    Join Date
    May 2002
    Posts
    36
    Thanks
    9
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='763640' date='05-Mar-2009 17:14']Validation appears to have become partially disabled. I could get it working again as follows:
    - Select G6:G200.
    - Open the Data Validation dialog.
    - Click OK (without changing anything).[/quote]

    Hans,

    Thanks again for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •