Forum Discussion
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?
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
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!!! 🙂
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().
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.
- Thanks, Peter! I love learning new Excel tricks.
