My goal is to create formulas in each cell in series so I do not have to fill
in each cells formula by hand. (would be very time consuming) The series of
references are not adjacent to each other but they do follow a specific
pattern. How can I use autofill, or some other usefull method, to finish my
worksheet?

Example:
A1=IF(sheet1! C2 =0,NA(),(MAX(sheet1! B2:B5 )+MIN(sheet1! B2:B5 )+sheet2! B2 )
B2=IF(sheet1! E2 =0,NA(), (MAX(sheet1! D2:D5 )+MIN(sheet! D2:D5 )+sheet2! C2 )
C2=IF(sheet1! G2 =0,NA(),(MAX(sheet1! F2:F5 )+MIN(sheet1! F2:F5 )+sheet2! D2)

(As it is the autofill will only give a series of which I can not use)