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?

  • 3
    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
  • 1
    Can you share a doc with us and show us the expected outcome? – Jacob Jan Tuinstra Mar 21 '13 at 20:32

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.

  • "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
  • 1
    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. – mhoran_psprep Feb 19 '12 at 13:22
  • You said that the formula is row dependent. Does that mean that the 4th row that is entered must always know that it was the 4th row? or do you mean that when you are sorting sometimes you forget to move the whole row? – mhoran_psprep Feb 19 '12 at 13:24
  • How do I make one axis based on one column of data and the other based on another column? – Danny Sep 17 '13 at 7:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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