Paste Link & Transpose?

Thanks:  0
Likes:  0

1. ## 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. ## 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 ??

3. ## 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. ## 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

5. ## 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. ## Re: Paste Link & Transpose?

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. ## 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!)

Talk about complicated - but it worked!

8. ## 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. ## 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. ## Re: Paste Link & Transpose?

Originally Posted by Nimrod
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!

## User Tag List

#### Posting Permissions

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