Creating a drop down which changes adjacent cell data

[officerjim]

Hello everyone, I'm revamping a key inventory for my company and would like to be able to track the history of each key (name of person who it was issued to, date issued, date returned). I figure the best way to do this for the 90 keys I currently have in circulation is to create drop down lists for each key that has been used by more than one person in its history and upon selecting a given person from the drop down list of keys used more than once, the adjacent cells would populate with the corresponding data of date issued and date returned. Some keys have not been used more than once so not every key will have a drop down. Here is what my key inventory spreadsheet looks right now.

Any help would be greatly appreciated!

[ChemistB]

Okay, so you want a dropdown in column C of Fob Inventory and that will automatically populate D, F and H of that row?

So this data is coming from Tab 2 "Data" which is currently empty. (Makes it tough to give you a formula)
How would "Data" be set up?

[officerjim]

Okay, so you want a dropdown in column C of Fob Inventory and that will automatically populate D, F and H of that row?

So this data is coming from Tab 2 "Data" which is currently empty. (Makes it tough to give you a formula)
How would "Data" be set up?

I would want a dropdown in column C that will auto populate D and F (H I can do manually for my purposes)

The data will be set up by having the FOB #s (which have had a history of more than one person using them) to have the dates for each person that has been issued one and when the FOB was returned.

I was thinking the Data sheet would have a table for each FOB that had more than one person use them in order to select from the dropdown in the FOB Inventory sheet and I'd use either the IF or VLOOKUP functions in some way but this is about as much as I know how to go about something like this with dates being involved. I found the attached xls that gets very close to what I want but when I tried to use dates instead of numbers it didn't come out properly.

[DaveDeV]

Hi OJ,

You may just be over-complicating the capture aspect...
If you create a table of "Key Movements" in addition to your primary "Key Register", you can use the Key Register as a validation list and lookup table for Key Details and then also create a table of "Key Holders" (past and present) and include them in the "Key Movements".

You can then create a pivot table to slice and dice the "Key Movements" to report in any way you want - by key, by keyholder, by date-range (who had what key, when") etc.

Just a thought...
Dave