Link row to Column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Aaronous - 17 Apr 2007 22:56 GMT

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.

Reply to this Message

Trevor Shuttleworth - 17 Apr 2007 23:08 GMT

Try:

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

and drag across the columns

Regards

Trevor

>I have a spreadsheet that has multiple tabs. I want to have a row of data
>be
[quoted text clipped - 8 lines]
> manually
> create the link for each one? I have about 70 or so and growing.

Reply to this Message

Peo Sjoblom - 17 Apr 2007 23:10 GMT

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

Signature

Regards,

Peo Sjoblom

>I have a spreadsheet that has multiple tabs. I want to have a row of data
>be
[quoted text clipped - 8 lines]
> manually
> create the link for each one? I have about 70 or so and growing.

Reply to this Message

JE McGimpsey - 17 Apr 2007 23:16 GMT

One way:

=INDEX(Sheet1!$A:$A,COLUMN())

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.

> 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:
[quoted text clipped - 6 lines]
> 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.

Reply to this Message

Aaronous - 17 Apr 2007 23:38 GMT

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.

> One way:
>
[quoted text clipped - 14 lines]
> > 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.

Reply to this Message

JE McGimpsey - 18 Apr 2007 00:11 GMT

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.

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

Reply to this Message

Aaronous - 18 Apr 2007 00:58 GMT

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.

> Put the formula I gave in Sheet2, cell A1, then copy to the right.
>
[quoted text clipped - 9 lines]
> > into the column worksheet. On sheet1, I want A1 to be A1 in sheet2. Then
> > sheet1!B1 to sheet2!A2 and so on.

Reply to this Message

Aaronous - 18 Apr 2007 01:08 GMT

never mind, I figured it out.

Reply to this Message