Excel - Link row to Column

Asked By Aaronou on 17-Apr-07 05:56 PM
I have a spreadsheet that has multiple tabs.  I want to have a row of data be
linked to a column on the other tab.  For example:

A1 linked to A1
B1 linked to A2
C1 linked to A3
D1 linked to A4

Is there an easy way to transpose the link so that I don't have to manually
create the link for each one?  I have about 70 or so and growing.

Trevor Shuttleworth replied on 17-Apr-07 06:08 PM

Cell A1:        =INDIRECT("Sheet2!A" & COLUMN(A1))

and drag across the columns


Peo Sjoblom replied on 17-Apr-07 06:10 PM
One way, select A1:A70, copy, select A2 in the other tab, do edit>paste
special and select paste link. While still selected press Ctrl + H, replace
= with ^=^
now A2:A70 in the other tab should be text strings, make sure they are still
selected and copy again, select A1 in the other tab and do edit paste
special and select transpose, do a reversed edit>replace and replace ^=^
with =
delete the help range in A2:A71
now A1:BR1 should be linked to A1:A70 in the first tab


Peo Sjoblom
JE McGimpsey replied on 17-Apr-07 06:16 PM
One way:


Please don't post the same message to multiple groups. It just tends to
fragment your answers, and it potentially wastes the time of those
answering questions that have already been answered.

In article <8EA38615-6B24-402B-8C2A-78E4189A4C45@microsoft.com>,
Aaronou replied on 17-Apr-07 06:38 PM
sorry, I realized I had originally posted in the Mac Excel page.

So I am confused about the index thing. I am pasting from the Row worksheet
into the column worksheet.  On sheet1, I want A1 to be A1 in sheet2.  Then
sheet1!B1 to sheet2!A2 and so on.
JE McGimpsey replied on 17-Apr-07 07:11 PM
Put the formula I gave in Sheet2, cell A1, then copy to the right.

Then Sheet2!B1 will get its value from Sheet1!A2, Sheet2!C1 will get its
value from Sheet1!A3, etc.

If you want the links to go the other way (it's not clear to me from
your post), switch Sheet1 and Sheet2.

In article <132D9846-25AB-4653-8285-C4CD221A807D@microsoft.com>,
Aaronou replied on 17-Apr-07 07:58 PM
I actually need to go the other way.  the data I need to copy is in the row.
I need to copy the row to the column.
Aaronou replied on 17-Apr-07 08:08 PM
never mind, I figured it out.
saeed replied on 12-Sep-09 02:08 PM
You are required to be a member to post replies.  After logging in or becoming a member, you will be redirected back to this page.
Alex YEUNG replied to Peo Sjoblom on 15-Jun-11 04:24 AM
I set up an account here just to let you know about this;

WTF; your method is so SMART!!!! Thanks a lot, you save my life.