copy formulas across two rows down without them skipping a row from the source data

[Morphies]

Hi all,

apologies for the poor title, not sure how be to word this one.

I'm trying to copy four rows of formula down around another 600 rows which I will then delete if they are not required...

the four rows and cell ranges are setup as follows:

A16 merged through i16

=IF(ISBLANK(QuoteDetails!A3)," ",QuoteDetails!A3)

A17

=IF(LEN(B17)<5,"",1)

b17 merged through G17

=(IF(ISBLANK(QuoteDetails!C3),"","Quantity: "&QuoteDetails!B3&" off"))&(IF(ISBLANK(QuoteDetails!B3),""," | Item: "&QuoteDetails!C3))&(IF(ISBLANK(QuoteDetails!D3),""," | Drg No: "&QuoteDetails!D3))&(IF(ISBLANK(QuoteDetails!E3),""," | Patt No: "&QuoteDetails!E3))&(IF(ISBLANK(QuoteDetails!F3),""," | Scope: "&QuoteDetails!F3))&(IF(ISBLANK(QuoteDetails!G3),""," | Specification: "&QuoteDetails!G3))

H17 merged with i17

=(IF(ISBLANK(QuoteDetails!C3),"","Per Unit: £"&(IF(ISBLANK(QuoteDetails!I3),QuoteDetails!H3,(QuoteDetails!H3/100)*(100-QuoteDetails!I3)))))

Then

A18 merged through I18

=IF(ISBLANK(QuoteDetails!A4)," ",QuoteDetails!A4)

a19

 =IF(LEN(B19)<5,"",A17+1)

B19 merged through G19

=(IF(ISBLANK(QuoteDetails!C4),"","Quantity: "&QuoteDetails!B4&" off"))&(IF(ISBLANK(QuoteDetails!B4),""," | Item: "&QuoteDetails!C4))&(IF(ISBLANK(QuoteDetails!D4),""," | Drg No: "&QuoteDetails!D4))&(IF(ISBLANK(QuoteDetails!E4),""," | Patt No: "&QuoteDetails!E4))&(IF(ISBLANK(QuoteDetails!F4),""," | Scope: "&QuoteDetails!F4))&(IF(ISBLANK(QuoteDetails!G4),""," | Specification: "&QuoteDetails!G4))

H19 merged with I19

=(IF(ISBLANK(QuoteDetails!C4),"","Per Unit: £"&(IF(ISBLANK(QuoteDetails!I4),QuoteDetails!H4,(QuoteDetails!H4/100)*(100-QuoteDetails!I4)))))

As you’ve probably guessed I’m trying to format data into adistributable format.

However when I try and drag a copy range A17 through I19 thenext set of formulas, beginning in A5 should read

=IF(ISBLANK(QuoteDetails!A5),"",QuoteDetails!A5)

reference to A5, but are actually referencingcells A7

Essentially I end up with two consecutive them two missing,so 1 2 5 6 9 10 13 14

Any way I can correct this?

TIA

 

[Morphies]

I found a solution based on this thread:

Welcome to the MrExcel board!

Without the volatile function INDIRECT.

In Sheet2 ..

1. In cell A1 put the formula: =INDEX(Sheet1!A$2:A$2352,CEILING(ROWS(A$1:A1),3)/3)

2. Select A1:A3 (note that A2 and A3 are blank)

3. Click & drag down the Fill Handle (the little black square at the bottom right of the selection)

Spreadsheet Formulas

Cell Formula A1 =INDEX(Sheet1!A$2:A$2352,CEILING(ROWS(A$1:A1),3)/3) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Essentially I built the dataset in a separate sheet ad used the index ceiling formula to reference them.

Cheers!