You need a table where you can register all drivers for all trucks along with start and end dates of assignment. And with a couple of helper columns. Like
Truck, Driver, DateAt, DateTo, DateToX, RowNumber (where DateToX = DateTo when DateTo is not empty, and TODAY() when it is empty, and RowNumber is calculated from 1 up to number of rows in this table).
Now in your original table, the driver can be calculated as INDEX from Driver column of TrucDrivers table with RowNumber as argument. And the RowNumber will be calculated using SUMIFS which returns RowNumber from TruckDrivers table where the Truck equals with one in current row in your original table, and DateAt being <= Date in current row of your original table, and DateToX being >= Date in current row of your original table.
Edit: You need a separate table, because mixing formulas and manual entries in same column is a surest way to disaster!
|