1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: transposing in Excel

Discussion in 'Business Applications' started by etaf, Apr 14, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. etaf

    etaf Moderator Thread Starter

    Joined:
    Oct 2, 2003
    Messages:
    65,371
    First Name:
    Wayne
    I have values in a spreadsheet
    In reference
    A1 B1 C1 D1 etc

    if i want to copy and paste a formual to transpose

    so for example
    in A10
    I have = A1
    in A11
    I have = B1
    in A12
    I have = C1

    is there a way to do that with copying

    I have copied the values and paste special value - transpose
    but that only copies values and does not pick up any changes
     

    Attached Files:

  2. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Highlight the values you would like to copy (A1 thru D1) -> Right-Click & Click Copy -> Click inside cell "A10" -> Right-Click & Click Paste Special -> Check 'Transpose' -> Click OK.

    The values should copy down as needed.
     
  3. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    So, if you make changes in A1 thru D1m you would like that to automatically change in A10 thru A13? Will the values ever go past D1?
     
  4. etaf

    etaf Moderator Thread Starter

    Joined:
    Oct 2, 2003
    Messages:
    65,371
    First Name:
    Wayne
    I'm not after the values - I want the reference to the cells

    so A10 becomes " =A1 "
    and A11 becomes " =B1"
     
  5. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    See if this is what you are after....

    1) Paste the following in A10.....=TRANSPOSE($A$1:$D$1)
    2) Press Enter
    3) Select A10
    4) Highlight from A10 down to A13 -> Press F2 -> Press CTRL+SHIFT+ENTER

    This should do what you are asking. I have re-attached a copy of the file you originally attached. Take a look at cells A15 thru A18.
     

    Attached Files:

  6. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Take a look at the INDIRECT function

    EDIT: Nevermind I just looked at your sample Workbook and see that this is not what you are looking for.

    Regards,
    Rollin
     
  7. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    This may seem a bit hokey :p- and there is no doubt a slicker way to do it - but at least this down and dirty will do the do until that comes along. It requires that you add an alpha column for reference. You could hide it if you choose.

    - Castleheart
     

    Attached Files:

  8. etaf

    etaf Moderator Thread Starter

    Joined:
    Oct 2, 2003
    Messages:
    65,371
    First Name:
    Wayne
    thanks rollin_again, I have been playing with the indirect and unable to make it work, so thanks for the update.

    The array version using =transpose has worked ok on my original - thanks gamecockfan

    Thanks CastleHeart, the transpose array worksOK - I have a large number of diferent ranges to do,
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - Solved transposing Excel
  1. estyMonsty
    Replies:
    0
    Views:
    338
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/703649

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice