Simple row references in Google Spreadsheets and resorting data

Question

This is my scenario

1. Users are entering data into rows with several columns
2. Last column is calculated on values of few previous columns (so it's row number independent).
3. While entering new and modifying existing data, calculations at the end are updated.
4. I will then sort data based on calculated column value (last column) so my rows get frequently reordered whenever there's new rows or existing get changed.

Problem

I would like to introduce an additional column where I could enter something that would reference some other row in the spreadsheet. Calculated value at the end would take this into account by adding referenced row's value and adding its calculated value to this rows calculation.

Example:

In the following example I would like the formula on the Calc column to be:

Calc = Calc_Ref + Val₁ × Val₂

This is some data:

Name     Val1   Val2   Ref     Calc[hidden column]
--------------------------------------------------
Tony     1      2              2
Frankie  2      3      first?  8 <--- BOTH ADDED!
Tony     1      1              1
Delboy   1      1              1
Rodney   4      3      third?  13 <-- BOTH ADDED!

Now when I would sort these rows according to Calc value, rows would get sorted on these values (1,1,2,8,13).

Possible tries

1. The easiest thing for users would of course be to reference row number as it appears on the left of the first column, but that would be a static value that would change when I will sort this data.

2. I could use an additional column with RowID where users entering new rows would apply it some sort of an ID. Preferably numbers. I could then use VLOOKUP formula to get the calculated value of the referenced row.

   But how would users enter this ID so it would be unique?

   1. They could just increment previous row number but that won't work after I resort because IDs will be mixed.
   2. I could simply use a formula to auto generate that number, but there's no formula that runs just once. Because if I used ROW this number would change every time I'd do a sort. but I would require this number to be set and then not change any more.

Question

What other choices do I have toward solving this problem?

Answer 1

Make the value in the name column unique. That way the row can always be tracked.

You will need to add a test to make sure that a new name is unique. I would suggest use the countif function.