I need to transpose a large worksheet. This worksheet in turn refers to absolute locations on the other sheet. It looks like this:
A B C
1 Alternatives Hardware Costs
2 PC desktops / Windows 7
3 Desktops (100%) Number 123
4 Capital expenses $123,456
5 Per client $789
C5 has a formula: ='Variables and sources'!$C$4
When I transpose via select > Copy > Paste > Transpose, this piece is transposed OK, except C5. I expected the formula to be left unchanged: it refers to a separate sheet; it uses an absolute reference; the MS article at http://office.microsoft.com/en-us/excel-help/switch-transpose-columns-and-rows-HP010224502.aspx says "To make sure that formulas continue to refer correctly to data in non-transposed cells, use absolute references in the formulas before you transpose them.".
The result though is ='Variables and sources'!$D$47. Why? Is there any way to solve this problem? I tried both Excel 2007 for Windows and Excel 2008 for Mac.
I am stumped: the worksheet is large; I do need to transpose it.