Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
Arithmetical Mode of Criteria in Multiple Non-Adjacent columns | Excel Worksheet Functions | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
Linking sheets when sorting row and column data | Excel Worksheet Functions |