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

Question

What other choices do I have toward solving this problem?

• The problem is not clear to me. Frankie Calc value is 8 because 2x3=6 and then 2 is added. Why 2? Because it is the value of Calc for the first row? Why sorting whould put last row on the first place? Have you to sort in descending order? Do you recognize that sorting alters the row positions and so what should happen to calc values which are based on row positions? – tic Mar 26 '12 at 9:25
• Can you share a doc with us and show us the expected outcome? – Jacob Jan Tuinstra Mar 21 '13 at 20:32

• "Name" only exists in this simplified example. What I'm doing is writing user stories (as per agile paradigm). They start with user role, that repeats. I may give them another column ID, but how can I ensure that users will increment the ID since IDs get mixed because of row ordering... This is also outlined in my question... And even if I'd use `countif`... Can you explain a bit further where to place and use this function? – Robert Koritnik Feb 19 '12 at 12:10