Forum Discussion

BerlinerTourGuide's avatar
BerlinerTourGuide
Copper Contributor
Jul 25, 2019

maintaining a cell reference despite changing the cell location through sorting

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 anoth...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 25, 2019

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

BerlinerTourGuide's avatar
BerlinerTourGuide
Copper Contributor
Aug 11, 2019

SergeiBaklan 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...😉

Resources