Forum Discussion
Excel: 2019 - Return STOP KILOMETER reading as START KILOMETER reading for new line in a table
Hi,
I am building a Vehicle tracking sheet (Created Table called TRACK), whereby the user enters the Registration No (RegNo), and needs to put in the Start Kilometers (START KM) and Stop Kilometers (STOP KM) in the row and the system will do some calculations.
Now what I want to do is, if that RegNo is entered in a new line in that table, it should automatically populate the START KM with the last available kilometers from the STOP KM column. (In other words, the new entry should start with STOP KM reading for that RegNo).
Alternatively, I have a MD table (Master Data), where I have the vehicles and its RegNo, etc. In this table, I wanted to put in a LOOKUP formula to return the last STOP KM reading. Then in the TRACKING Table, in the START KM column, I put in a VLOOKUP formula to obtain the last STOP KM reading and display it in the START KM column.
I have looked at tutorials on how to use the LOOKUP, COUNTA etc. formulas, but I am a bit slow to grasp the explanations.
Attached is the excel spreadsheet. Can you please help or guide me with this?
2 Replies
RaynoJ Dynamically look up the latest Stop KM from the Master table will not work as the TRACK table will then insert the same value in all rows for a particular vehicle.
The way to go would be to dynamically pick-up the highest Stop KM for a vehicle from the preceding rows in the TRACK table, using MAXIFS. The attached workbook does just that, although I have probably committed a great offence by giving up structured table references in the Start KM column.
To be honest, I don't really know how to fix that, but perhaps somebody else can.
Note also that you need to enter the first Stop KM for all your vehicles in the very beginning of the TRACK table. Otherwise, the Start KM formula finds no value.
Riny_van_Eekelen I get what you are saying in your very last sentence. Thanks for your input though, however, it is not exactly what I need. I will continue scouring the internet and hopefully pick up some tips and tricks on how to achieve what I require.
