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
Jul 25, 2019

SergeiBaklan : 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... ;-)

Resources