Sign in
 
Home20132010Other VersionsLibraryForumsGallery
TechCenter >
> 
> 
Excel 2010 - Transpose data that contains formulas

Answered Excel 2010 - Transpose data that contains formulas

  • Thursday, June 21, 2012 2:57 PM
     
     
    Sign In to Vote
    0

    Hi

    I have some data in a worksheet which contains formulas - set out in one column. I want to copy this data and paste it into another worksheet but to have the data going across the way instead of down the way. I've tried using Paste Special and Transpose but I just get #REF!. If I use Transpose and Paste Values I get the number to do what I want but I don't get the formulas and I need them. Any ideas.

    Viv

    Viv Haig

     

All Replies

  • Thursday, June 21, 2012 4:06 PM
     
     
    Sign In to Vote
    0
    That depends on the formulas - Excel will try to transpose the cell references in the formulas but that may not always be appropriate.

    Regards, Hans Vogelaar

     
  • Tuesday, June 26, 2012 1:36 AM
    Moderator
     
     
    Sign In to Vote
    0

    Hi,

    Just checking in to see if the suggestion was helpful. Please let us know if you would like further assistance.


    Best Regards, 

    Rex Zhang

    TechNet Community Support

     
  • Wednesday, June 27, 2012 8:39 AM
     
     
    Sign In to Vote
    0

    Hi

    The formulas are just basic SUM ones. I've tried Googling for an answer too but haven't found a solution.

    It may be that it's just not possible to transpose cells which ontain formaulas and to keep the formulas.

    Thanks, Viv

    Viv Haig

     
  • Saturday, June 30, 2012 8:02 AM
     
     
    Sign In to Vote
    0
    The formula is just something simple like =C2.

    Viv Haig

     
  • Saturday, June 30, 2012 9:36 AM
     
     Answered
    Sign In to Vote
    0

    A possible explanation:

    Let's say that the formula =C2 is in cell E2, and that you paste / transpose it to cell D2.

    Excel interprets the formula as 'return the value of the cell 2 columns to the left of the cell with the formula'.

    When you transpose, that becomes 'return the value of the cell 2 rows above the cell with the formula'.

    But there is no cell 2 rows above D2. So you get #REF!

    Regards, Hans Vogelaar

    • Marked As Answer by VHaig Monday, July 02, 2012 10:23 AM
    •  
     
  • Monday, July 02, 2012 10:22 AM
     
     
    Sign In to Vote
    0

    Thanks Hans. I didn't think it would be possible. It was worth asking though.

    Viv

    Viv Haig