How to make Excel a potent relational data source for manual entry?

Question

I am using Excel to store data kind of as a relational database for data that will be entered manually (see my other question here : https://stackoverflow.com/questions/9416263/possible-solutions-for-simple-data-persistence-and-manual-entry)

However my biggest problem so far is making the RELATIONSHIPS easier (in a manual entry perspective). As of now, I reference other tables by using the incremental ID, but when you look at such a foreign key, the user has no idea what the Object #42 is, the user has to go and seek the #42 in the other table in order to know what it is.

Let me give an example, let's say we only have two tables and 1 one-to-many relationship between Cities and Countries.

The problem is I do the relationships using the articial ID key, but it has no meaning and when adding cities, I have to check the ID for the referenced country in the other tab.

Is there any way I could select the country using dynamic combo-boxes that uses the data from the other table? If so, can it show the country name, but enter the ID (because that's how the relations are made).

Do I HAVE to change the primary keys to be the country name and get rid of the ID fields where necessary? Because some tables are the result of many-to-many relationships and are defined by multiple foreign keys, so they have no unique value except the combined foreign keys OR an artificial ID.

I hope I was clear enough, and I must admit that I am an Excel newbie.

Answer 1

As some comments pointed it out, I am trying to use the wrong tools for my needs. I should be using Access instead of Excel, especially since I have resolved my ODBC drivers problem

Answer 2

You can use data validation to force possible values for a field. See this article from MS support.

Following your example, I succeed in linking the Country_City field with the Name_Country field. Though the data validation does not let you bind a multicolumn list (such as a list of (key,value).