Copy records that are 4 rows apart

P

Pablo

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!
 
P

Pete_UK

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
 
P

Pablo

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
 
P

Pete_UK

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
 
P

Pablo

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top