Forum Discussion

TomStyles's avatar
TomStyles
Copper Contributor
Mar 06, 2020

Help needed on formula/VBA for auto fill

Hi All,   First post here, so hello and glad to join in.   I'm trying to create a sheet so I can track my mileage for work.  I travel to specific destinations on a regular basis so am hoping to s...
Formulas and Functions
Macros and VBA
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Mar 07, 2020

TomStyles Doable, but don't think you need VBA for this. Choose one of several functions that can lookup the route information from one table (LOOKUP, VLOOKUP, INDEX combined with MATCH, XLOOKUP) and return the milage in another. It depends a bit on how your data looks like.

 

TomStyles's avatar
TomStyles
Copper Contributor
Mar 09, 2020

Riny_van_Eekelen Thanks so much for coming back to me. Data set looks like this:

 

Journey DateDetails of RouteActual Mileage
01/10/2019HO-BOD-HO 
02/10/2019HO-TRU-HO 
08/10/2019HO-FAL-HO 

 

I was thinking of setting the Route Details Column to a set dropdown list and then once I've clicked on the desired route I'm hoping the Actual Mileage cell will auto fill the correct mileage.

 

Many thanks

 

Tom

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Mar 09, 2020

    TomStyles Ooops. Edited my file as it contained a small error!

    • TomStyles's avatar
      TomStyles
      Copper Contributor
      Mar 12, 2020

      Thanks Riny_van_Eekelen, you've been so helpful.  I really appreciate it!  What was the error as you didn't attach the updated file :).

       

      So by using VLOOKUP I can set up a separate sheet with all the preset routes and miles and pop that formula in the mileage column on the original sheet.

Resources