LocationData sheet + TimeData sheet (referential integrity)

Donald Herring

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.

Mark Cronk

Hi Donald,

You can use a drop down with your location IDs. It just can't be dynamic; the dropdown won't go out and collect values from another source. If you make a change to your locations you'll have to adjust your dropdown values. The PivotApp has some ability to assist with that if you have access to it.

I would use the location names in the drop down and then lookup the number.

Happy to help further.

Mark