Autofilling incremental numbers in a formula

[Guest]

I need to figure out how to auto fill a formula: =IF('Actual
Sched'!CS22=0,'Actual Sched'!CS21,'Actual Sched'!CS22) to become =IF('Actual
Sched'!CS26=0,'Actual Sched'!CS25,'Actual Sched'!CS26) and more increasing
the cell number by four for each line.

Any wisdom to share?

 

[Guest]

One way ..

Replace:
=IF('Actual Sched'!CS22=0,'Actual Sched'!CS21,'Actual Sched'!CS22)

with:
=IF(INDIRECT("'Actual Sched'!CS"&ROW(A1)*4+18)=0,INDIRECT("'Actual
Sched'!CS"&ROW(A1)*4+17),INDIRECT("'Actual Sched'!CS"&ROW(A1)*4+18))

Then copy the cell down

 

[T. Valko]

Here's the non-volatile version:

=IF(INDEX('Actual Sched'!CS:CS,ROWS($1:1)*4+18)=0,INDEX('Actual
Sched'!CS:CS,ROWS($1:1)*4+17),INDEX('Actual Sched'!CS:CS,ROWS($1:1)*4+18))

Biff