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.


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.


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

Calc = CalcRef + Val1 × Val2

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.


What other choices do I have toward solving this problem?


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

Using the example you have in your question use the countif() function in another column to check that the value in the first column is unique. Check the name against all values in the name column. If countif() returns 1 it means that name only appears once.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.