1

I have an MS Excel 365 (with current patches) spreadsheet that tracks mileage and tolls to various locations. I am trying to build in logic that won't populate mileage and tolls if I have 2 entries to the same location on the same day (as it would be one trip not two).

Today I am using VLookUps to populate the mileage and tolls from a separate sheet in the same workbook. They work, but don't have the logic to verify if the destination is the same location on the same day. I have separate VLookUps for Mileage and Tools (as they are separate cells), but I am trying to build this logic into a single formula and have it populate both of the cells.

Current Screen shot from the spreadsheet

Spreadsheet details

  • Where D2 & D3 are dates
  • Where H2 & H3 are locations
  • $A$4:$G$210 is a list of locations to search from to match from Column H on the that has Mileage to the locations in column F on the alternate sheet and Tolls in Column G on the alternate sheet.

Both VLOOKUPs work in the cells as stand alone formulas
=VLOOKUP(H3,'Payment info'!$A$4:$G$210,6,0)
=VLOOKUP(H3,'Payment info'!$A$4:$G$210,7,0)

Here is the formula as it stands today (not working):
=IF ($D$2<>$D$3), SetCellValue($R$3,(VLOOKUP(H3,'Payment info'!$A$4:$G$210,6,0)))), (IF(($H$2<>$H$3), SetCellValue($S$3(VLOOKUP(H3,'Payment info'!$A$4:$G$210,7,0)),SetCellValue($R$3,"NOTHING")))

This formula in Cell R4 produces this error message. Error message from the formula in cell R4

It is possible that the values in D2 & D3 (rows 3 & 4) could be the same but the values in H3 & H4 could be different (rows 3 & 4). In the first case, I want the values from the alternate page to populate R3 & S3, but R4 & S4 to be blank or "--". in the second case, I want values to be populates in R5 & S5, but not R4 & S4.

If the dates are the same, then I want to compare the values from H3 & H4 (locations) to see if they are the same. If they are then blank out R4 & S4. If they are different, then populate R3 & S3 with the values from the VLookUp on the alternate sheet.

Screenshot and mock up of data in Columns R & S. Note Row 4 being populated with "--" :
Screen shot and mock up of data in Columns R & S. Note Row 4 being populated with "--"

This all makes sense to me so if it's not clear, please let me know.

cgru
  • 21

1 Answers1

1

My original hope to populate information into 2 cells with a formula in a single cell was not able to be done in Excel without using VBA (not how I wanted to solve the problem as it would be overly complex).

What I had to do was add logic into the existing VLookUP formulas so that if had 2 events at the same location on the same day, it would not count the mileage and tolls twice, but if I went to the same location 2 days in a row, it would still populate the mileage and the toll values in the correct cells for the second day only as I go back to the office/home overnight. I discovered this additional logic was necessary and I used an AND function to set the criteria for the IF. here are the updated and correct formulas:

  • Adv Logic MILEAGE (column P)--> =IF(AND(H4=$H5,$D4=$D5),"--",(VLOOKUP($H5,'AltSheetWithMileage&TollInfo'!$A$4:$G$210,6,0)))
  • Adv Logic Tolls (column Q)--> =IF(AND(H4=$H5,$D4=$D5),"--",(VLOOKUP($H5,'AltSheetWithMileage&TollInfo'!$A$4:$G$210,7,0)))

Spreadsheet with working formulas & logic

Thank you to both @ReddyLutonadio & @Emily for their initial help on getting me onto the correct path.

cgru
  • 21