5

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. This is the way I enter data

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.

Community
  • 1
  • 1
dominicbri7
  • 2,479
  • 4
  • 23
  • 33
  • 5
    Respectfully, it seems as if you're using the wrong tool for this. Microsoft Access would be better suited. – Robert Harvey Feb 23 '12 at 16:16
  • Agree with Robert, you're trying to recreate core functionality of Access, just use Access. – Jesse Feb 23 '12 at 21:07
  • 1
    Respectfully, No. That's complete bullshit. 1.) Yes you can do this. 2.) Excel can even host its own DB engine (or connect to other db-engine backends if you must) -- in Excel 2010 you need the PowerPivot add-in -- in Excel 2013 it's built-in. http://www.microsoft.com/en-us/bi/powerpivot.aspx – BrainSlugs83 May 02 '13 at 00:36

2 Answers2

3

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

dominicbri7
  • 2,479
  • 4
  • 23
  • 33
1

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).

Alberto De Caro
  • 5,147
  • 9
  • 47
  • 73
  • You are right here, validation can help me, but as others have pointed out I am using the wrong tool for what I want to achieve.. I'll look into Access instead of Excel – dominicbri7 Feb 27 '12 at 15:54