Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Paste Link & Transpose?

This is a discussion on Paste Link & Transpose? within the Excel Questions forums, part of the Question Forums category; Hi, Does anyone know of a way to copy and paste as a link but transpose the data at the ...

  1. #1
    New Member
    Join Date
    May 2002
    Location
    Australia
    Posts
    25

    Default Paste Link & Transpose?

    Hi,

    Does anyone know of a way to copy and paste as a link but transpose the data at the same time?

    I need to copy a range of cells in a column and paste into another sheet as a row and have the row update if the column updates.

  2. #2
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259

    Default Re: Paste Link & Transpose?

    IF it's a problem with the reference cells changing then make your references absolut before transposing them to the new sheet.
    Eg if link is =A1 then change to $A$1 before transposing ... does this help or am I mis-understanding you ??

    <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

  3. #3
    New Member
    Join Date
    May 2002
    Location
    Australia
    Posts
    25

    Default Re: Paste Link & Transpose?

    Thnaks for the response.

    The problem is the reference cells are not always formulas but values. eg. Sheet 1: A1=10, A2=20, A3=30 to be sent to Sheet 2: B1, C1, D1.

    If the values in Sheet 1 change, we want them reflected in Sheet 2. Formulas are much easier as you can always make them absolute as you say.

  4. #4
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259

    Default Re: Paste Link & Transpose?

    Though Im sure there's a better way I got this to work

    Lets assume you want to transpose formula in B1:D1 of Sheet2 that links to A1:A3 of Sheet1 ... OK ?

    DO all steps in Sheet 2:

    Step 1:
    in cell A1 put =sheet1!$A1

    Step 2:
    Drag formula from A1 down to A3

    Step 3:
    Copy Range A1:A3 to another blank column eg H1:H3

    Step 4:
    Select the new pasted cells eg H1:H3 and Copy

    Step 5:
    Select B1 and special paste ... transpose the H1:H3 cells..

    Dont ask me why it has to be copied to another area first , but the steps above worked for me :o

    <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

  5. #5
    New Member
    Join Date
    May 2002
    Location
    Australia
    Posts
    25

    Default Re: Paste Link & Transpose?

    Sorry it's taken so long to get back to you Nimrod.

    I'll give that a try.

    Thanks for your help and a Merry Christmas to you.

    Clinton.

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    66,265

    Default Re: Paste Link & Transpose?

    Quote Originally Posted by clintonjh
    Thnaks for the response.

    The problem is the reference cells are not always formulas but values. eg. Sheet 1: A1=10, A2=20, A3=30 to be sent to Sheet 2: B1, C1, D1.

    If the values in Sheet 1 change, we want them reflected in Sheet 2. Formulas are much easier as you can always make them absolute as you say.
    =INDEX(Sheet1!$A$1:$A$5,COLUMN()-COLUMN($B$1)+1)

    will transpose as desired. The COLUMN($B$1) bit refers to the formula cell where it is first put.

  7. #7
    New Member
    Join Date
    Jun 2009
    Posts
    2

    Default Re: Paste Link & Transpose?

    I know this is an old post but it's top of google when you search for this problem.

    I just had the same issue and putting dollar symbols infront of references didn't work when I was using the transpose function (it kept changing the grid references ).

    Then I found the solution (which would be impossible to get on your own!)

    http://excel.tips.net/Pages/T002652_Transposing_and_Linking.html
    Talk about complicated - but it worked!

  8. #8
    New Member
    Join Date
    Jun 2009
    Posts
    2

    Default Re: Paste Link & Transpose?

    The logic is that you copy the data (the links) you want to transpose to another worksheet and convert it into text by putting a sign infront of it. You then copy and transpose and then convert back to formulas. Very clever!

  9. #9
    New Member
    Join Date
    Jul 2010
    Posts
    1

    Default Re: Paste Link & Transpose?

    Use the transpose function. This must be entered in as an array and where it is located must cover the correct count of rows or columns that you are transposing.

  10. #10
    New Member
    Join Date
    Oct 2009
    Posts
    2

    Default Re: Paste Link & Transpose?

    Quote Originally Posted by Nimrod View Post
    Though Im sure there's a better way I got this to work

    Lets assume you want to transpose formula in B1:D1 of Sheet2 that links to A1:A3 of Sheet1 ... OK ?

    DO all steps in Sheet 2:

    Step 1:
    in cell A1 put =sheet1!$A1
    ...
    Thanks a lot. Works great!

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com