Autofilling incremental numbers in a formula

M

Math Geek

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?
 
M

Max

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

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
 
Top