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!

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!

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!

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!

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!

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!