Forum Discussion

hlutze2217's avatar
hlutze2217
Copper Contributor
May 12, 2024

Transposing Absolute References

I would like to drag a sum down a column, essentially creating field sums for a large number of records. Once dragged down, I want to paste-special transpose. The final result should be a row of sums, but the sums should row sums from above. I could turn all the sums into absolute references first, but that is tedious. Somehow, I would like to highlight multiple cells and ask Excel to turn them all into absolutely references at the same time. Can this be done?

  • dscheikey's avatar
    dscheikey
    Bronze Contributor

    hlutze2217 

    Hi, if we were here at Ask LibreOffice, the answer would be very easy. In LibreOffice you can switch on absolute references for all selected cells at the same time by pressing F4. There is also a trick with which you can transpose non-absolute references and the formulas will still work. CTRL+X CTRL+Z CTRL+V (or transpose).

    Unfortunately, this does not work with Excel. Here I would recommend that you convert the formulas in the column into absolute references using the search and replace function. So if your formulas are all in column B then you can use search 'B' replace '$B$' to rewrite them all at the same time. However, this only works if your initial formula is very simple and does not contain any 'B' otherwise. With =SUM(B1:B3) it will work.

    Kind regards

    Jürgen

     

    • hlutze2217's avatar
      hlutze2217
      Copper Contributor

      Thank you for your quick response! Found a trick... or I guess just a function... If I use '=TRANSPOSE(<column of sums>)' in a new cell, it keeps the row sum references!!! 🙂

       

      • dscheikey's avatar
        dscheikey
        Bronze Contributor

        hlutze2217 

        I thought you wanted to delete or move the original sum functions in the column after transposing. If the original formulas remain in place, you can work with TRANSPOSE().

  • hlutze2217 

    Are you absolutely wedded to the idea of dragging single-cell formulas based upon the concept of relative referencing?  If the objective is simply to get a result, array formulas would provide the result you describe.  Using 365

    = TOROW(BYROW(array, SUM))

    Using older generations of Excel

    = TRANSPOSE(MMULT(array, {1;1;1;1;1}))

    entered with CSE would give a result for a 5-column array. 

    • hlutze2217's avatar
      hlutze2217
      Copper Contributor
      Thanks, Peter! I love learning new Excel tricks.

Share