LocationData sheet + TimeData sheet (referential integrity)
I have one sheet that records location data:
Location ID (autonumber), Location Name
I need to record lots of data for this location over time so it would seem the best way is to add another sheet with:
Location ID (reference to other sheet's Location ID), TimeData
I know there is no way (which is irritating) to create a dropdown in the TimeData sheet to make a dropdown list of the Location Name in the LocationData sheet. This will make updating the TimeData somewhat painful and prone to human error.
Is there any way to reduce the time necessary to enter data into the TimeData sheet when one has to constantly switch back to the LocationData sheet to search/find the Location ID so it can be added to the TimeData?
Should I scrap the database notion of "ID" and just use "Location Name" only for both sheets?
NOTE: This is an over simplification of the columns for the purpose of being concise.