Autofill but with a custom rule??

[Guest]

Hi,

Is it possible to autofill with custom rules. Example:

Cell A1 contains a row of IF functions with references to let's say C4, G4
and H4. If i autofill this normally A2 would contain the same IF functions
but now with references to C5, G5 and H5 unless $ is used of course. My
dilemma is that I want Excel to fill A2 with the same If function as A1 but
with references now to C6, G6 and H6 and then when i autofill A3 I want it to
contain the same IF function referring to C8, G8 and H8, so on and so forth.
Since I paste data in to one sheet and then use another sheet to compile it
this feature would be very helpful to me but I realize that it's unlikely
that this is possible... still wanted to ask though.

Regards
Mitch

 

[Guest]

Yes it is, have a look at this formula:
=SUM(INDIRECT("G"&ROW(2:2)*2&":H"&ROW(2:2)*2))*INDIRECT("C"&ROW(2:2)*2)
which is equal to =sum(G4:H4)*C4 but when copied down becomes
=sum(G6:H6)*C6 and so on

the trick is to use the INDIRECT and the ROW() functions.

Hope this helps!
JG