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...
excel
Formulas and Functions
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
May 12, 2024

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
    May 12, 2024

    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's avatar
      hlutze2217
      Copper Contributor
      May 12, 2024
      That would be ideal, but I can just give the column width=0.

Resources