Bound cell formula to a sorted table-CodePudding

I have a table at A1:C5 and also formula at H50 =C1 C2 and H51 =C3 C4 C5

How can I sort the table by column B without messing up H50 and H51? (I want that the formulas will be 'bound' to the cells even if the row position is changing)

Thanks.

CodePudding user response：

Since you only have a few cells in the formulas, you can assign each cell in range C1 to C5 individual range names, then use these names in the formulas in H50 and H51. By addressing the cells with their name, the result will not change when the named cell changes position.

CodePudding user response：

H50     =INDEX(C:C,1) INDEX(C:C,2)
H51     =INDEX(C:C,3) INDEX(C:C,4) INDEX(C:C,5)
        or if the rest of C:C is empty:
H51     =SUM(C:C)-H50

Alternatively, in another column (possibly on another sheet), where they won't get rearranged by sorting:

X1:X5   =@C:C
H50     =SUM(X1:X2)
H51     =SUM(X3:X5)

Regarding named ranges, you could give a name to C:C and refer to it by name. For example if C:C has prices for headphones you could name C:C as HeadphonePrice and use the names in formulas: H50 =INDEX(HeadphonePrice,1) INDEX(HeadphonePrice,2). Bit of a tradeoff - you know what the formula is referring to but lose the immediate connection to where it's referring.

There are better ways but this crude approach I know works without booting up my Windows laptop.