Add rows to a formula when copying

  • Thread starter Thread starter Jarvi
  • Start date Start date
J

Jarvi

I have the following formula in cell C3

=IF((Calculations!C9-Calculations!C15)=0,"
n/a",Calculations!C3/(Calculations!C9-Calculations!C15))

I wish to copy this to cell C16 but wish to add more than the 13 row
(ie 16 - 3) to each reference in the worksheet Calculations. I need i
to add 27 rows.

The end result formula I need in cell C16 is

=IF((Calculations!C36-Calculations!C42)=0,"
n/a",Calculations!C30/(Calculations!C27-Calculations!C42))

Is there any easy way to do this? Happy to do it using a macro i
necessary. To sum up, I would like to copy a formula and add
constant number of rows to the formula at the same time.

Thanks,

Jame
 
Perhaps this side-pitch might find the strike-zone ..

If you put:

in A16: Calculations!C
in B16: 27
(B16 contains the # of rows to be added to the formula in C3)

and then you put in C16:

=IF(INDIRECT(A16&(9+B16))-INDIRECT(A16&(15+B16))=0,"n/a",INDIRECT(A16&(3+B16
))/(INDIRECT(A16&(9+B16))-INDIRECT(A16&(15+B16))))

this should return in C16 (untested) the equivalent of
[original post corrected for a presumed typo (the "27" should be "30")]:
=IF((Calculations!C36-Calculations!C42)=0,"
n/a",Calculations!C30/(Calculations!C36-Calculations!C42))
 
Back
Top