| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy records that are 4 rows apart
Dear reader,
I have a bit of a problem copying from one worksheet to another. I need to copy some dates from worksheet B to worksheet A. Currently these worksheets are set up this way WORKSHEET A - A B 1 Part Date 2 xy 3/24/08 Formula used: =(worksheetb!B5) 3 yz (I would like a formula that will skip four rows and copy cell B9 WORKSHEET B (contains the data I need to copy) A B 1 Part Date 2 3 (Cells A2:A5 are merged) 4 5 xy 3/24/08 6 7 (Cells A6:A9 are merged) 8 9 yz 3/25/09 Please help! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy records that are 4 rows apart
In cell B2 of Worksheet A put this formula:
=INDIRECT("worksheetb!B"&ROW(A1)*4+1) Format as a date, then copy the formula down for as many rows as are required. Hope this helps. Pete On Mar 26, 8:58*pm, Pablo wrote: Dear reader, I have a bit of a problem copying from one worksheet to another. *I need to copy some dates from worksheet B to worksheet A. *Currently these worksheets are set up this way WORKSHEET A - * * *A * * * * *B 1 Part * * *Date 2 * xy * * * 3/24/08 * * Formula used: =(worksheetb!B5) * 3 * yz * * * *(I would like a formula that will skip four rows and copy cell B9 WORKSHEET B *(contains the data I need to copy) * * *A * * * * *B 1 Part * * * Date 2 3 *(Cells A2:A5 are merged) 4 5 * xy * * * * *3/24/08 * * * 6 7 *(Cells A6:A9 are merged) 8 9 *yz * * * * * 3/25/09 Please help! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy records that are 4 rows apart
Hi Pete,
I tried this formula, and it keeps saying that the result is "volatile". I even tried changing the A1 logical value from True to False to further experiment, but it still didn't work. The formula is finding the target row, but it is returning a #REF! error. I look forward to any additional input. Cheers, Pablo "Pete_UK" wrote: In cell B2 of Worksheet A put this formula: =INDIRECT("worksheetb!B"&ROW(A1)*4+1) Format as a date, then copy the formula down for as many rows as are required. Hope this helps. Pete On Mar 26, 8:58 pm, Pablo wrote: Dear reader, I have a bit of a problem copying from one worksheet to another. I need to copy some dates from worksheet B to worksheet A. Currently these worksheets are set up this way WORKSHEET A - A B 1 Part Date 2 xy 3/24/08 Formula used: =(worksheetb!B5) 3 yz (I would like a formula that will skip four rows and copy cell B9 WORKSHEET B (contains the data I need to copy) A B 1 Part Date 2 3 (Cells A2:A5 are merged) 4 5 xy 3/24/08 6 7 (Cells A6:A9 are merged) 8 9 yz 3/25/09 Please help! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy records that are 4 rows apart
I've not come across that message before - are you using XL2007, and is this
another one of those "helpful" messages that MS programmers seem prone to use? Changing the value in A1 will have no effect on this formula - the ROW(A1) part returns the number 1, which gets multiplied by 4 and has 1 added on (giving 5). When it is copied down it becomes ROW(A2), ROW(A3), ROW(A4) etc, returning 2, 3, 4 etc, which in turn become 9, 13, 17, which are the rows you want to access. Did you use the correct worksheet name in your formula? If you have spaces in the sheet name then you will have to include apostrophes, like so: =INDIRECT("'worksheet b'!B"&ROW(A1)*4+1) (one after the first quote and one before the exclamation mark). Hope this helps. Pete "Pablo" wrote in message ... Hi Pete, I tried this formula, and it keeps saying that the result is "volatile". I even tried changing the A1 logical value from True to False to further experiment, but it still didn't work. The formula is finding the target row, but it is returning a #REF! error. I look forward to any additional input. Cheers, Pablo "Pete_UK" wrote: In cell B2 of Worksheet A put this formula: =INDIRECT("worksheetb!B"&ROW(A1)*4+1) Format as a date, then copy the formula down for as many rows as are required. Hope this helps. Pete On Mar 26, 8:58 pm, Pablo wrote: Dear reader, I have a bit of a problem copying from one worksheet to another. I need to copy some dates from worksheet B to worksheet A. Currently these worksheets are set up this way WORKSHEET A - A B 1 Part Date 2 xy 3/24/08 Formula used: =(worksheetb!B5) 3 yz (I would like a formula that will skip four rows and copy cell B9 WORKSHEET B (contains the data I need to copy) A B 1 Part Date 2 3 (Cells A2:A5 are merged) 4 5 xy 3/24/08 6 7 (Cells A6:A9 are merged) 8 9 yz 3/25/09 Please help! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy records that are 4 rows apart
Pete,
It worked! I inserted the apostrophes and the target column to the new formula. Thanks a lot for your help! This formula really helps us a lot. Cheers, Pablo "Pete_UK" wrote: I've not come across that message before - are you using XL2007, and is this another one of those "helpful" messages that MS programmers seem prone to use? Changing the value in A1 will have no effect on this formula - the ROW(A1) part returns the number 1, which gets multiplied by 4 and has 1 added on (giving 5). When it is copied down it becomes ROW(A2), ROW(A3), ROW(A4) etc, returning 2, 3, 4 etc, which in turn become 9, 13, 17, which are the rows you want to access. Did you use the correct worksheet name in your formula? If you have spaces in the sheet name then you will have to include apostrophes, like so: =INDIRECT("'worksheet b'!B"&ROW(A1)*4+1) (one after the first quote and one before the exclamation mark). Hope this helps. Pete "Pablo" wrote in message ... Hi Pete, I tried this formula, and it keeps saying that the result is "volatile". I even tried changing the A1 logical value from True to False to further experiment, but it still didn't work. The formula is finding the target row, but it is returning a #REF! error. I look forward to any additional input. Cheers, Pablo "Pete_UK" wrote: In cell B2 of Worksheet A put this formula: =INDIRECT("worksheetb!B"&ROW(A1)*4+1) Format as a date, then copy the formula down for as many rows as are required. Hope this helps. Pete On Mar 26, 8:58 pm, Pablo wrote: Dear reader, I have a bit of a problem copying from one worksheet to another. I need to copy some dates from worksheet B to worksheet A. Currently these worksheets are set up this way WORKSHEET A - A B 1 Part Date 2 xy 3/24/08 Formula used: =(worksheetb!B5) 3 yz (I would like a formula that will skip four rows and copy cell B9 WORKSHEET B (contains the data I need to copy) A B 1 Part Date 2 3 (Cells A2:A5 are merged) 4 5 xy 3/24/08 6 7 (Cells A6:A9 are merged) 8 9 yz 3/25/09 Please help! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy records that are 4 rows apart
Glad to hear that, Pablo - thanks for feeding back.
Pete "Pablo" wrote in message ... Pete, It worked! I inserted the apostrophes and the target column to the new formula. Thanks a lot for your help! This formula really helps us a lot. Cheers, Pablo "Pete_UK" wrote: I've not come across that message before - are you using XL2007, and is this another one of those "helpful" messages that MS programmers seem prone to use? Changing the value in A1 will have no effect on this formula - the ROW(A1) part returns the number 1, which gets multiplied by 4 and has 1 added on (giving 5). When it is copied down it becomes ROW(A2), ROW(A3), ROW(A4) etc, returning 2, 3, 4 etc, which in turn become 9, 13, 17, which are the rows you want to access. Did you use the correct worksheet name in your formula? If you have spaces in the sheet name then you will have to include apostrophes, like so: =INDIRECT("'worksheet b'!B"&ROW(A1)*4+1) (one after the first quote and one before the exclamation mark). Hope this helps. Pete "Pablo" wrote in message ... Hi Pete, I tried this formula, and it keeps saying that the result is "volatile". I even tried changing the A1 logical value from True to False to further experiment, but it still didn't work. The formula is finding the target row, but it is returning a #REF! error. I look forward to any additional input. Cheers, Pablo "Pete_UK" wrote: In cell B2 of Worksheet A put this formula: =INDIRECT("worksheetb!B"&ROW(A1)*4+1) Format as a date, then copy the formula down for as many rows as are required. Hope this helps. Pete On Mar 26, 8:58 pm, Pablo wrote: Dear reader, I have a bit of a problem copying from one worksheet to another. I need to copy some dates from worksheet B to worksheet A. Currently these worksheets are set up this way WORKSHEET A - A B 1 Part Date 2 xy 3/24/08 Formula used: =(worksheetb!B5) 3 yz (I would like a formula that will skip four rows and copy cell B9 WORKSHEET B (contains the data I need to copy) A B 1 Part Date 2 3 (Cells A2:A5 are merged) 4 5 xy 3/24/08 6 7 (Cells A6:A9 are merged) 8 9 yz 3/25/09 Please help! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| number of rows/records for the specified period | Excel Worksheet Functions | |||
| Copy specific records from a worksheet to another | Excel Worksheet Functions | |||
| How do I copy records with specific text from one wkbk to another | Excel Worksheet Functions | |||
| Checking for duplicate records / rows | Excel Discussion (Misc queries) | |||
| Removing all duplicate records except one copy | Excel Discussion (Misc queries) | |||
Linear Mode
