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

I had this same problem - so infuriating! I could transpose 2-4 lines of formulas just fine but any larger selections and everything turned out a mess (or usually about 2-4 lines at the bottom of the transposed results would be correct) - as far as I can see this is just a glaring bug!?

I had sheets with over 27000 cells that needed transposing - creating named ranges for each cell was NOT an option :)

I eventually created some messy code to do it line for line

Sub transpose()
    ' gummih transpose, takes individual columns and pastes as rows
    Dim firstLine As Integer
    Dim lastLine As Integer
    Dim lineToStartPasting as Integer
    Dim cols As Integer
    firstLine = 2
    lastLine = 80
    lineToStartPasting = 89 'pasting starts at "A90"

    For cols = 1 To 100
        Range(ActiveSheet.Cells(firstLine , cols ), ActiveSheet.Cells(lastLine , cols )).Select
        Selection.Copy

        ActiveSheet.Cells(lineToStartPasting + cols , 1).Select
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            False, transpose:=True
    Next cols 
End Sub

Answer 2

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.