maintaining a cell reference despite changing the cell location through sorting

Copper Contributor

if the position of a value is changed through a sorting procedure the reference to the value in another cell is lost... e.g.: I reference cell A1 filled with the value 1 to which I reference in another cell; I now sort rows in a way so that the value 1 moves to cell A3 und the value 3 is filled into cell A1, then the reference in the other cell is changed to 3. how can I maintain the reference to the value 1, i.e. to the cell that was 'moved'?

thanks!

BerlinerTourGuide

7 Replies
Filters do not follow the rules of maintaining cell references. The function =A1 references the cell A1 not the value in A1. What I mean by this is that when you filter your table, =A1 will always refer to whatever value is held in cell A1, not the value that was originally in cell A1 before the filter was initiated. If your table permits it, VLOOKUP() may be a viable solution to your problem.

@BerlinerTourGuide ,

The workaround with the reference could be organize as

(assuming your data is in column A starting from cell A1 and you reference on the value of the cell in 15th position, other words on A15 in unfiltered range)

=INDEX($A:$A,
  COUNTA($A$1:INDEX($A:$A,ROW(A15)-ROW($A$1)+1)) -
  SUBTOTAL(103,$A$1:INDEX($A:$A,ROW(A15)-ROW($A$1)+1)) +
  ROW(A15)-ROW($A$1)+1
)

Not very practical...

@PReagan: thanks, but you repeat just the problem (which I have already understood). I'm looking for the solution... ;)

ps: and I was not talking about filtering (which is even more tricky); I was talking about sorting...

@Sergei Baklan : thanks, but indeed: not very practical...

I have lots of rows which belong to different categories (marked out in various columns). now I'd like - in a simple way) to sort (not filter, like PReagan said) the rows according to the categories, then form sums of the grouped row-categories, reference them, and then change the order of the rows again... so what would be needed is an automatic transformation of e.g. a1:a3 into a1+a2+a3 and then links that move automatically with each of the re-sorted cells...

   note: this is the first time that I could not solve a task with excel... ;)

@Sergei Baklan and 'the world': well, I have a work-around, at least for the special, but very frequent case of sums: if you had marked a certain minor block of numbers as a sum, and then change the order completely via sorting, the sum would be wrong afterwards... to avoid this one must open an auxiliary column (which can be removed afterwords from view); in this column one references the first number of the block, then drags down to the end of the block; so you reference all intended numbers of the block; then in the (visible) column that 'counts' you write a sum that includes all lines of your sheet (as many lines as there are, from the first to the last... so you may have: sum(a1:a1000) but only three numbers in that column...); now you can change the order of the lines as you whish, and the sum remains correct... well, almost... excel may change the range of the sum... (annoying...); so either adjust the range which is quickly done, or place the sum in the first line or another line whose position is not changed...;)

@BerlinerTourGuide 

I can.t help thinking that the problem stems from the 'quick and dirty' philosophy that pervades spreadsheet practice (though it is responsible for its huge success in terms of adoption).  The idea of clicking on a cell to reference it by location (A1 etc.) on the sheet means that the user action is incorporated into the model but their intent is not evident. 

 

In the present case, the data might be a list or, less likely, an array.  If it were an array, then order within the array is significant (data is access by an index or a combination of indices) and on no account should the order be changed by sorting.  Preventing sorting or at least identifying it as an error is more important than coping with it.

 

Assuming your data is actually a list, then sorting is an inherent property of a list and direct references to other records by location should not be used because the property that is not conserved under a sort operation.  Reference is normally performed through the use of primary/foreign key matches which pick up the required data irrespective of its location.  If, as you suggest, there is a requirement to aggregate a block of values, then members of the block need to be identifiable from their attributes.  SUMIFS will pick up the correct values irrespective of their sort order.

 

In summary, you get what you pay for.  Cheap and nasty shortcuts get the initial job done fast but can easily go wrong … which might go a long way towards explaining the fact that 3rd-party audits have shown that fewer than 1 in 10 spreadsheets are actually correct.

 

Note:  As an aside, if you are processing lists, ensure that they are held as Tables.  If it is simply a range, then it is only too easy to sort some of the fields whilst leaving others in their original order.  This destroys the integrity of the data and is irrecoverable once options for 'Undo' have been exhausted.

@BerlinerTourGuide 

 

If you are looking for say the second largest value of a group, there is the LARGE function.

 

https://www.excelchamp.net/finding-the-second-largest-value-in-excel/#:~:text=%3DLARGE(A1%3AA5%2C,la....

 

But, the fail-safe way is to use INDIRECT, example =INDIRECT("A1"), as noted here:

 

https://superuser.com/questions/1390320/is-there-a-way-to-absolute-the-referred-or-target-cell