Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

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
share|improve this answer
add comment

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.

share|improve this answer
    
Interesting. It could have been a solution. I have actually around 60 variables defined on the "Variables and sources" worksheet. I was able to create Names in bulk using Create from Selection. However, the problem is applying these names. Whenever I try to apply them I get "Microsoft Excel cannot find any references to replace.". I found this KB article about this problem, but it only applies to Excel 2000: support.microsoft.com/kb/268974. Secondly, what in the world is the purpose of names' scope = Workbook if I can't apply a name on any other worksheet? –  DKroot May 23 '11 at 21:30
    
Make sure that the cell references in your name include the sheetname. By setting the scope to Workbook, the name can be used in all other sheets in your file, but not in any other workbooks that you have open. Setting the scope to a single sheet means you can only use that name within that sheet. To call on a "local" name from another sheet, precede it with the sheet name (e.g. Sheet 1!MyRange). For more info: office.microsoft.com/en-us/excel-help/… –  Ellesa May 23 '11 at 22:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.