Change cell ref when formula is copied down

**ABSTRAKTUS** · 01-16-2012, 11:45 AM

Hi Gurus,

Can't find a formula that would look in every row when it is copied down to let's say every fourth row. Example:

Source (Sheet1):

A1=A
A2=B
A3=C
etc.

The results would read in Sheet2:

A1=A
A5=B
A9=C
etc.

Any ideas?

Thanks in advance chaps!

**Andrew-R** · 01-16-2012, 11:59 AM

In cell A1 on Sheet 2 put the formula:

=IF(MOD(ROW(),4)<>1,"",INDEX(Sheet1!A:A,INT((ROW()-1)/4)+1))

**ABSTRAKTUS** · 01-16-2012, 12:14 PM

Brr... Can't work it out! The source data starts in B5 going down (Sheet1), The results should show in cells: D5, D12, D19, D26, D33 etc. so every seventh row (just realized I have merged cells). Where it went wrong? I've changed 4's to 7's in your formula, Sheet1!A:A is now Sheet1!B:B and still doesn't work...

**Andrew-R** · 01-16-2012, 12:44 PM

My formula is dependent on the data and the formula both starting in row 1.

Easily fixed, though...

=IF(MOD(ROW()-4,7)<>1,"",INDEX(Sheet1!$B$5:$B$500,INT((ROW()-5)/7+1)))