Paste Link & Transpose?

clintonjh

New Member
Joined
May 14, 2002
Messages
25
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.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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 ??
 
Upvote 0
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.
 
Upvote 0
Though Im sure there's a better way I got this to work :confused:

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 :eek:
 
Upvote 0
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.
 
Upvote 0
clintonjh said:
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.
 
Upvote 0
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!
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
Though Im sure there's a better way I got this to work :confused:

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!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top