Excel: referencing a table cell

Question

In Excel I have a table, with the first column acting as a unique identifier (I make sure there aren't duplicates). I want to be able to reference a cell of this table's first column so that: - if I change the value of a referenced cell, the references get updated automatically as well - if I change the table sorting, the references to a cell with a certain value keep reference to the cell with that value

Is it even possible in Excel without using VBA? Thank you in advance.

More details on my specific case

I'm designing a turn-based battle game, in which characters have different attacks. I've got an Attacks table with the following columns:

• Attack name
• Type
• Target Mode
• Cost
• Power
• etc...

The Attack name column contain unique values: I use it as a unique identifier for each attack.

Then I have a Characters table, with the following columns:

• Name
• Max HP
• Strength
• (...other not relevant columns...)
• Attack 1
• Attack 2
• Attack 3

The last three columns should reference a row of the Attacks table. I want to be able to validate these cells so that only Attacks table's Attack name column values can be entered and, more important, if I change the value of an Attack name in the Attacks table, the corresponding value in the Characters table gets updated automatically with the new value. Also, should I sort the Attacks table differently, the references in the Characters table should keep pointing to the original Attack names.

I hope it is more clear what I'm trying to achieve. If something is not clear, just ask me and I'll try to provide additional details. Thank you in advance for your help.

My tables data

Attacks table

Characters table

In my case, for example, I would like that, if I change the Attack name in the Attacks table for the Water Attack and rename it to Rain Attack, the value in row Water Creature and column Attack 1 of the Characters table would automatically change, from Water Attack to Rain Attack.

Answer 1

Here's a "quick" fix, check if this already works for you, if not let me know where the problems are:

• Select an "attack"-cell in your character table, for example the the cell K2 (K: column "Attack1", 2: row "Water Creature").
• Type "=" into the cell, this will "start" a formula (don't hint enter yet)
• go to your attack table and select the appropriate attack, in this case it's cell A2 (A: column "Attack name, 2: row "Water Attack"): the formula will look similar like this: "=Tabelle1!A2"
• hit enter

Now you "referenced" the "attack cell" in the character table with the "attack name cell" in the attack table. All changes in to the Attack name will be visible in the character sheet as well (try changing Water Attack to Rain Attack to see what I mean). You "move" the row in the attack table by using the sorting function, and use cut&paste the whole row some where else. The reference will still work. If you use copy the reference will "stay" with the original row.