Problem with transpose in Excel 2007/2008: Excel transposes even absolute references

Question

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.

Answer 1

Try using a named range/cell.

Go to Formula > Name Manager > New

Enter your preferred name e.g. "per_client"

In "Refers To" enter the following (don't forget the "="):

='Variables and sources'!$C$4

In cell C5, use this formula:

=per_client

Transpose your data range again.