Linking a column with a row
 

I'm trying to set up Excel to link several rows with several columns. I
guess I could do it manually with the "=" function for everyone, but that'd
take a long time and a chance of a mistake is fairly high. What I have is 2
sheets, On sheet two, I have a chemical that is broken down into 8 pieces of
data going from H3 to H10, I then have a space and then repeat for a another
chemical with 8 pieces of data from H12 to H19 and I repeat for several
hundred.

On sheet one, I need to have those 8 pieces show up in a row, so H3 from
sheet two shows up in N9, H4 shows up in O9, H5 in P9 and so on for all
eight. If I try to propagate it down, instead of N10 linking to H12 from
sheet two, it links to H4.

Is there a way to get it to link column to row and propagate it down without
having to goto each individual piece and using the "=" function??

Thanks,

Linking a column with a row
 

Try this:

assuming your data in sheet 2 starts in row 3, if not you will need to
adjust to suit, so in N9 put:
=INDIRECT("Sheet2!H"&ROW(1:1)*9-6)
copy across 7 columns (8 columns in total), for each column copied across
substrac 1 from the last parameter of the formula, so in O9 you would end up
with:
=INDIRECT("Sheet2!H"&ROW(1:1)*9-5)
in P9
=INDIRECT("Sheet2!H"&ROW(1:1)*9-4)
the last column should be:
=INDIRECT("Sheet2!H"&ROW(1:1)*9+1)
Now select N9 thru U9 and copy down as far as needed.

HTH
JG


"bimmu" wrote:

I'm trying to set up Excel to link several rows with several columns. I
guess I could do it manually with the "=" function for everyone, but that'd
take a long time and a chance of a mistake is fairly high. What I have is 2
sheets, On sheet two, I have a chemical that is broken down into 8 pieces of
data going from H3 to H10, I then have a space and then repeat for a another
chemical with 8 pieces of data from H12 to H19 and I repeat for several
hundred.

On sheet one, I need to have those 8 pieces show up in a row, so H3 from
sheet two shows up in N9, H4 shows up in O9, H5 in P9 and so on for all
eight. If I try to propagate it down, instead of N10 linking to H12 from
sheet two, it links to H4.

Is there a way to get it to link column to row and propagate it down without
having to goto each individual piece and using the "=" function??

Thanks,

Linking a column with a row
 

Enter this formula in N9 of Sheet1:

=INDEX(Sheet2!$H:$H,COLUMNS($A:C)+9*ROWS($1:1)-9)

Then copy across to U9.
Then, select N9 to U9, and copy that *selection* down as far as needed.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"bimmu" wrote in message
...
I'm trying to set up Excel to link several rows with several columns. I
guess I could do it manually with the "=" function for everyone, but
that'd
take a long time and a chance of a mistake is fairly high. What I have is
2
sheets, On sheet two, I have a chemical that is broken down into 8 pieces
of
data going from H3 to H10, I then have a space and then repeat for a
another
chemical with 8 pieces of data from H12 to H19 and I repeat for several
hundred.

On sheet one, I need to have those 8 pieces show up in a row, so H3 from
sheet two shows up in N9, H4 shows up in O9, H5 in P9 and so on for all
eight. If I try to propagate it down, instead of N10 linking to H12 from
sheet two, it links to H4.

Is there a way to get it to link column to row and propagate it down
without
having to goto each individual piece and using the "=" function??

Thanks,