Results 1 to 10 of 10
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Copy-Paste Special-Transpose From Another File (2000)

    Whenever I copy and paste special-transpose, say, a column of entries from one file to a row in another file using the paste special-transpose keystrokes, I can't seem to find a way to get rid of the constant "$" sign from the first cell that causes the entries in all of the cells in the second file to contain the contents of the first cell in the first file I am copying from. Anyone have a hint?

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Copy-Paste Special-Transpose From Another File (2000)

    Sorry, didn't follow your post. Can you attach a small example of your source workbook and let us know the range that you want to copy? Thanks! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Copy-Paste Special-Transpose From Another File (2000)

    Sam,
    Thanks for your reply. I'm not sure I can attach two files as examples, so I'll try and explain the problem again: I want to copy, say, cells A1 through A12 of File No. 1 to cells A1 through L1 of File No. 2. When I do so, using the Copy Special/Transpose, each of the cells in File No. 2 (i. e., cells A1 through L1) result in the same contents as cell A1 of File No. 1, i. e., "FileNo.1$A$1". That is, the transposition doesn't seem to take effect to result in the contents of cells A2-A12 of File No. 1 being copied over to cells B1-L1 in File No. 2.
    Make any sense?
    Thanks,
    Jeff

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Copy-Paste Special-Transpose From Another File (2000)

    Jeff, seems clear enough to me, but I cannot duplicate your results. Works just fine for me. What happens if you copy, press the new button, and paste special? --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Copy-Paste Special-Transpose From Another File (2000)

    Hi Jeff,
    What is in the cells you're copying? Are they formulae or values and can you give an example of what you start with and what you end up with?
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Copy-Paste Special-Transpose From Another File (2000)

    Sam/Rory,
    My fault-what I want to do is transpose the actual links to the various cells in the first file to the second.
    Sorry about that!
    Jeff

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Copy-Paste Special-Transpose From Another File (2000)

    Not sure that I exactly understand, but what I usually do is
    1) select the original cells
    2) global replace the = with a ~, so that Excel thinks they are just text
    3) copy & paste special/transpose
    4) global replace the ~ with =[BookN]SheetM!

    HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Copy-Paste Special-Transpose From Another File (2000)

    Excel doesn't do exactly what you want, links to other sheets are standardly set as absolute, and you'll have to automate the following manual steps:

    1. Create the link from the target to the source for the first cell only, * but one cell off from where you want the final range
    2. Edit the link in the target cell to remove the absolute references '$', using F2 then F4
    3. Copy the single link cell across or down to derive all the links
    3. Now Copy, Paste Special, Transpose, bearing in mind that you cannot Paste over the source in Transpose mode, hence the * above

    DTH?
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Copy-Paste Special-Transpose From Another File (2000)

    I'm sorry-I guess I'm slow; could you walk me through an example?
    Thanks,
    Jeff

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Copy-Paste Special-Transpose From Another File (2000)

    I composed an answer, thought I had posted it, but it isn't here. <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> And I hope this will be more clear, I had some disinformation in my previous post.

    There are two ways to paste links:

    1. Copy the source cell(s). Activate (switch to by using Ctrl-Tab) the target WB, select Edit, Paste Special, Links. Ranges can be pasted, and references are pasted as relative.

    2. In the target cell in the target WB, start edit mode, enter = and then activate the source WB, select the source cell and press Enter. The cell will be set with absolute references.

    From your original post I think you are using the latter.

    Disadvantage of method 1 is that if you paste transpose the relative referenced cells, they won't point where they are supposed to; you have to make them absolute first by going through each one in edit mode (F2) and hitting F4, then the transpose will work.

    Disadvantage of method 2 is that it's cell-by-cell; takes longer to set up but then the transpose is easier. Or, you can do just one cell, turn the absolute references to relative, copy it down, then you end up at the same point as method 1.

    Best way to handle this would be to set a up a paste links macro which then transposes the pasted references; I started on it earlier but the masters of my paycheck demanded my attention. Sammy had it pointed in the right direction, above.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •