Now i have my formula that works I want to paste it into every row of a column BUT when i do it makes all cell references relative to that row, which is fine apart from some cell references on another worksheet that I do not want to change!

My formula is:

=IF(AND(K2="Yes",M2="Compatible"),'Data Lists'!E5,IF(J2=0,'Data Lists'!E6,IF(M2="Remanufactured",'Data Lists'!E3,IF(M2="Compatible",'Data Lists'!E2,"Select Template"))))

I want all the 'Data Lists' cell refs to be fixed and not change but want all the other cell refs to change relative to the rows as i paste. i.e. pasting the above formula into row 3 with the following result:

=IF(AND(K3="Yes",M3="Compatible"),'Data Lists'!E5,IF(J3=0,'Data Lists'!E6,IF(M3="Remanufactured",'Data Lists'!E3,IF(M2="Compatible",'Data Lists'!E2,"Select Template"))))

Hope this makes sense.

Many thanx in advance.

Boz