This is my scenario
- Users are entering data into rows with several columns
- Last column is calculated on values of few previous columns (so it's row number independent).
- While entering new and modifying existing data, calculations at the end are updated.
- 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).
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.
I could use an additional column with
RowIDwhere users entering new rows would apply it some sort of an ID. Preferably numbers. I could then use
VLOOKUPformula to get the calculated value of the referenced row.
But how would users enter this ID so it would be unique?
- They could just increment previous row number but that won't work after I resort because IDs will be mixed.
- I could simply use a formula to auto generate that number, but there's no formula that runs just once. Because if I used
ROWthis 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?