|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Auto-Entering Values into a Column in a Table based on Value in Adjacent Column
I have the following table:
Date Truck No. Driver Name Location Is it possible to have the Driver Name auto-filled into the column cell as soon as I enter the Truck No., based on historical entries in the table? |
#2
|
|||
|
|||
Probably - please provide an example worksheet
|
#3
|
|||
|
|||
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! |
Tags |
auto-fill |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Formula to check combinations of values in one column to find match from another column | kong1802 | Excel | 1 | 06-15-2018 05:26 AM |
Need to delete duplicate of a column B based on column A and keep ColumnB if unique value to columnA | enigmaprince | OneNote | 0 | 08-23-2017 01:30 PM |
Insert values from multiple rows based on value in one column | pachmarhi | Excel | 3 | 07-18-2014 09:57 PM |
Mouseover cell to indicate mouse pointer location based on Specific Row/Column values | bolandk | Excel | 1 | 05-15-2014 08:22 AM |
Auto-create row from column values | ReviTULize | Excel | 1 | 03-28-2013 02:27 AM |