| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I automatically get information from sheet 1 into sheet 2
Information on sheet 1 is in columns with 24 rows, i.e. B1:B24, B26:B50 and
so on. Sheet 2 is in rows of 24 columns with names corresponding to the columns on sheet 1 i.e. A2:W2 Then B2:X2. I can get the data if I manually select each cell on sheet 2 & =Sheet1!B2 & so on. Is there a formula to accomplish this as each set of imported data to the rows on sheet 2 are 24 row numbers apart. (sheet 2 b4 = sheet 1 c50 & sheet 2 b5 = sheet 1 b74). Thanks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I automatically get information from sheet 1 into sheet 2
It would be nice if your explanations matched your examples.
You say 24 rows on Sheet1, but you then mention B26 to B50 ... *25* rows. So ... is it B26 to B49, or ... B27 to B50? Next, you say Sheet2 utilizes sets of 24 columns. Then you state A2 to W2 ... which is *23* columns, And B2 to X2 ... which is also *23* columns. Would you care to clarify your *exact* locations (ranges)? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Cardslinger" wrote in message ... Information on sheet 1 is in columns with 24 rows, i.e. B1:B24, B26:B50 and so on. Sheet 2 is in rows of 24 columns with names corresponding to the columns on sheet 1 i.e. A2:W2 Then B2:X2. I can get the data if I manually select each cell on sheet 2 & =Sheet1!B2 & so on. Is there a formula to accomplish this as each set of imported data to the rows on sheet 2 are 24 row numbers apart. (sheet 2 b4 = sheet 1 c50 & sheet 2 b5 = sheet 1 b74). Thanks |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I automatically get information from sheet 1 into sheet 2
The TRANSPOSE function might be helpful.
The tricky part is entering an array formual - suggest you use the first of the two examples in Help and carefully follow the instructions for entering an array to see how this works. I think this example will require only a minor extension to meet your needs. "Cardslinger" wrote: Information on sheet 1 is in columns with 24 rows, i.e. B1:B24, B26:B50 and so on. Sheet 2 is in rows of 24 columns with names corresponding to the columns on sheet 1 i.e. A2:W2 Then B2:X2. I can get the data if I manually select each cell on sheet 2 & =Sheet1!B2 & so on. Is there a formula to accomplish this as each set of imported data to the rows on sheet 2 are 24 row numbers apart. (sheet 2 b4 = sheet 1 c50 & sheet 2 b5 = sheet 1 b74). Thanks |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I automatically get information from sheet 1 into sheet
Sorry, I did not mention that I had a blank row on sheet 1 between each
entry. So cells on sheet 1 are - A2:A25 then A26:A49 etc. (A25 & A49 is blank). Sheet 2 is from A2:W2 then A3:W3 then A4:W4. Hope this helps BTW I'm using '07 Thanks "RagDyer" wrote: It would be nice if your explanations matched your examples. You say 24 rows on Sheet1, but you then mention B26 to B50 ... *25* rows. So ... is it B26 to B49, or ... B27 to B50? Next, you say Sheet2 utilizes sets of 24 columns. Then you state A2 to W2 ... which is *23* columns, And B2 to X2 ... which is also *23* columns. Would you care to clarify your *exact* locations (ranges)? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Cardslinger" wrote in message ... Information on sheet 1 is in columns with 24 rows, i.e. B1:B24, B26:B50 and so on. Sheet 2 is in rows of 24 columns with names corresponding to the columns on sheet 1 i.e. A2:W2 Then B2:X2. I can get the data if I manually select each cell on sheet 2 & =Sheet1!B2 & so on. Is there a formula to accomplish this as each set of imported data to the rows on sheet 2 are 24 row numbers apart. (sheet 2 b4 = sheet 1 c50 & sheet 2 b5 = sheet 1 b74). Thanks |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I automatically get information from sheet 1 into sheet 2
Hi Cardslinger,
If you just want to get the data from a column in sheet1 to a row in sheet2 you can: 1. Select the B1:B24 range and click Copy. Select A2 on sheet2 and choose Edit, Paste Special, and check Transpose. 2. If you need to build a formula then: on sheet2 select the range A2:W2 and enter the formula =TRANSPOSE(Sheet1!B1:B24), but don't press Enter. Instead press Shift Ctrl Enter. 3. You can also use the OFFSET function without the need to do an array entry as follows: =OFFSET(Sheet1!$B$1,COLUMN(A1)-1,0) -- Cheers, Shane Devenshire "Cardslinger" wrote: Information on sheet 1 is in columns with 24 rows, i.e. B1:B24, B26:B50 and so on. Sheet 2 is in rows of 24 columns with names corresponding to the columns on sheet 1 i.e. A2:W2 Then B2:X2. I can get the data if I manually select each cell on sheet 2 & =Sheet1!B2 & so on. Is there a formula to accomplish this as each set of imported data to the rows on sheet 2 are 24 row numbers apart. (sheet 2 b4 = sheet 1 c50 & sheet 2 b5 = sheet 1 b74). Thanks |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I automatically get information from sheet 1 into sheet
Enter this formula on Sheet2, in A2:
=INDEX(Sheet1!$A$2:$A$96,(24*ROWS($A$1:A1))-24+COLUMNS($A$1:A1)) Then copy across to W2. While A2 to W2 are *still* selected from the "copy", Click the fill handle of that 23 cell selection, And drag down to row 5. This will copy 4 sets of 23 rows. If you need more sets, just adjust the ranges in the formula. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Cardslinger" wrote in message ... Sorry, I did not mention that I had a blank row on sheet 1 between each entry. So cells on sheet 1 are - A2:A25 then A26:A49 etc. (A25 & A49 is blank). Sheet 2 is from A2:W2 then A3:W3 then A4:W4. Hope this helps BTW I'm using '07 Thanks "RagDyer" wrote: It would be nice if your explanations matched your examples. You say 24 rows on Sheet1, but you then mention B26 to B50 ... *25* rows. So ... is it B26 to B49, or ... B27 to B50? Next, you say Sheet2 utilizes sets of 24 columns. Then you state A2 to W2 ... which is *23* columns, And B2 to X2 ... which is also *23* columns. Would you care to clarify your *exact* locations (ranges)? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Cardslinger" wrote in message ... Information on sheet 1 is in columns with 24 rows, i.e. B1:B24, B26:B50 and so on. Sheet 2 is in rows of 24 columns with names corresponding to the columns on sheet 1 i.e. A2:W2 Then B2:X2. I can get the data if I manually select each cell on sheet 2 & =Sheet1!B2 & so on. Is there a formula to accomplish this as each set of imported data to the rows on sheet 2 are 24 row numbers apart. (sheet 2 b4 = sheet 1 c50 & sheet 2 b5 = sheet 1 b74). Thanks |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I automatically get information from sheet 1 into sheet
RagDyer's post accomplished the task.
Thanks "RagDyer" wrote: Enter this formula on Sheet2, in A2: =INDEX(Sheet1!$A$2:$A$96,(24*ROWS($A$1:A1))-24+COLUMNS($A$1:A1)) Then copy across to W2. While A2 to W2 are *still* selected from the "copy", Click the fill handle of that 23 cell selection, And drag down to row 5. This will copy 4 sets of 23 rows. If you need more sets, just adjust the ranges in the formula. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Cardslinger" wrote in message ... Sorry, I did not mention that I had a blank row on sheet 1 between each entry. So cells on sheet 1 are - A2:A25 then A26:A49 etc. (A25 & A49 is blank). Sheet 2 is from A2:W2 then A3:W3 then A4:W4. Hope this helps BTW I'm using '07 Thanks "RagDyer" wrote: It would be nice if your explanations matched your examples. You say 24 rows on Sheet1, but you then mention B26 to B50 ... *25* rows. So ... is it B26 to B49, or ... B27 to B50? Next, you say Sheet2 utilizes sets of 24 columns. Then you state A2 to W2 ... which is *23* columns, And B2 to X2 ... which is also *23* columns. Would you care to clarify your *exact* locations (ranges)? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Cardslinger" wrote in message ... Information on sheet 1 is in columns with 24 rows, i.e. B1:B24, B26:B50 and so on. Sheet 2 is in rows of 24 columns with names corresponding to the columns on sheet 1 i.e. A2:W2 Then B2:X2. I can get the data if I manually select each cell on sheet 2 & =Sheet1!B2 & so on. Is there a formula to accomplish this as each set of imported data to the rows on sheet 2 are 24 row numbers apart. (sheet 2 b4 = sheet 1 c50 & sheet 2 b5 = sheet 1 b74). Thanks |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I automatically get information from sheet 1 into sheet
You're welcome, and appreciate the feed-back.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Cardslinger" wrote in message ... RagDyer's post accomplished the task. Thanks "RagDyer" wrote: Enter this formula on Sheet2, in A2: =INDEX(Sheet1!$A$2:$A$96,(24*ROWS($A$1:A1))-24+COLUMNS($A$1:A1)) Then copy across to W2. While A2 to W2 are *still* selected from the "copy", Click the fill handle of that 23 cell selection, And drag down to row 5. This will copy 4 sets of 23 rows. If you need more sets, just adjust the ranges in the formula. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Cardslinger" wrote in message ... Sorry, I did not mention that I had a blank row on sheet 1 between each entry. So cells on sheet 1 are - A2:A25 then A26:A49 etc. (A25 & A49 is blank). Sheet 2 is from A2:W2 then A3:W3 then A4:W4. Hope this helps BTW I'm using '07 Thanks "RagDyer" wrote: It would be nice if your explanations matched your examples. You say 24 rows on Sheet1, but you then mention B26 to B50 ... *25* rows. So ... is it B26 to B49, or ... B27 to B50? Next, you say Sheet2 utilizes sets of 24 columns. Then you state A2 to W2 ... which is *23* columns, And B2 to X2 ... which is also *23* columns. Would you care to clarify your *exact* locations (ranges)? -- Regards, RD ------------------------------------------------------------------------- -- Please keep all correspondence within the NewsGroup, so all may benefit ! ------------------------------------------------------------------------- -- "Cardslinger" wrote in message ... Information on sheet 1 is in columns with 24 rows, i.e. B1:B24, B26:B50 and so on. Sheet 2 is in rows of 24 columns with names corresponding to the columns on sheet 1 i.e. A2:W2 Then B2:X2. I can get the data if I manually select each cell on sheet 2 & =Sheet1!B2 & so on. Is there a formula to accomplish this as each set of imported data to the rows on sheet 2 are 24 row numbers apart. (sheet 2 b4 = sheet 1 c50 & sheet 2 b5 = sheet 1 b74). Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula for automatically entering information from a master sheet | Excel Discussion (Misc queries) | |||
| create a formula in one sheet that would read data from separate sheet automatically | Excel Discussion (Misc queries) | |||
| can entered data in sheet 1 be automatically pasted in sheet 2 | Excel Discussion (Misc queries) | |||
| Extracting information from records to another sheet automatically | Excel Worksheet Functions | |||
| Extracting information from records to another sheet automatically | Excel Worksheet Functions | |||
Linear Mode
