How to restore formulas of table rows to normal state after sorting in excel?-CodePudding

I have a table in excel whose data are resulted by formulas. For example, A2 cells equals E7*2 of another sheet as follow.

When I sort table from largest to smallest, the order of formulas changes. For example, formulas of A2 is E18*2 of another sheet as follow.

Except Ctrl z or its shortkey, is there any solution to restore table formulas to initial state? specially if I saved the file and want to open it after a while. I mean formulas of A2 becomes E7 not E18.

Thanks

CodePudding user response：

I don't think that you can have both: sorting and referencing other cells. What you can do is to build one formula that does the multipling and the sorting.

If you have Excel 365 you can use this formula:

=SORT(CHOOSE({1,2},rowdata[Num1]*2,rowdata[Num2]),1,-1)

CHOOSE re-builds the rowdata table but multiplies the values of the first column by two.

Caveat of this solution: you can't use a table for the result.

CodePudding user response：

I feel like a real geek for writing this, but I have done something similar and I have found a really funny solution:

Sheet1 :

Col_A          Col_B    Col_C
    a              b        c
        =5-Sheet2!A2
        =5-Sheet2!A3
        =5-Sheet2!A4

Sheet 2:

Col_A
    a
    1
    2
    3

So my "Sheet1" looks like this:

Col_A   Col_B   Col_C
    a       b       c
            4
            3
            2

When I sort Col_B in "Sheet1", I get the order 2, 3, 4 but how to get it back?

Well, in Col_C I have put =FORMULATEXT(B2) (and the same for C3 and C4, obviously), this gave something like:

Col_A   Col_B          Col_C
    a       b              c
            4   =5-Sheet2!A2
            3   =5-Sheet2!A3
            2   =5-Sheet2!A4

Now, when I order according to Col_B, I get 2, 3, 4, and if I want to get it back, I just order on Col_C, and everything gets ordered back as it was.