Autofill Nonconsecutive with formula

[Jasuan]

First time poster.

I have the following formula: =IF(DATA!B69=0,DATA!B69,(DATA!A202&" "&DATA!B69))
The first entry is in Column BO; Row 2. The next entry would go into Column BS; Row 2.
The formula in the second entry should be =IF( DATA!B72 =0,DATA!B72,(DATA!A202&" "&DATA!B72))

As I have 40 entries for this specific formula (equally spaced but non-contiguous) I would like if I could write a formula to have it auto fill it or a VBA code to do it.

Any help is appreciated in advance.

 

[ukmikeb]

Hi

Welcome to the MrExcel forum.

Try this in BO2 :-

=IF(INDIRECT("DATA!$B"&69+((COLUMNS($BO:BO)-1)/4)*3)=0,INDIRECT("DATA!$B"&69+((COLUMNS($BO:BO)-1)/4)*3),DATA!$A$202&" "&INDIRECT("DATA!$B"&69+((COLUMNS($BO:BO)-1)/4)*3))[code]

drag across to BR2.
Then select BO2 to BR2 and drag across as far as required.

hth

 

[Jasuan]

Hi

Welcome to the MrExcel forum.

Try this in BO2 :-

=IF(INDIRECT("DATA!$B"&69+((COLUMNS($BO:BO)-1)/4)*3)=0,INDIRECT("DATA!$B"&69+((COLUMNS($BO:BO)-1)/4)*3),DATA!$A$202&" "&INDIRECT("DATA!$B"&69+((COLUMNS($BO:BO)-1)/4)*3))[code]

drag across to BR2.
Then select BO2 to BR2 and drag across as far as required.

hth[/QUOTE]

Mike,

    Thanks for the hospitality and quick response. This worked like a charm! Now I am getting familiar with the INDIRECT function and learning how to write a formula like this for future needs.