Forum Discussion
Formula move between sheets
I might have solved this by accident. When one uses Copy and Paste, it changes the formulas to refer to a cell address in the same relative position to the cell being copied but when your Cut and Paste, it maintains the "absolute" cell address references in the formula. After I used Cut and Paste it automatically included the Checklist! reference solving my issue.
="The form of Contract is the "&Checklist!E17&IF(AND(Checklist!D16="Yes",Checklist!D18="Yes")," All parties have access to the complete signed MoA and applicable standard specifications for this contract. ","")&IF(AND(Checklist!D19="Yes",Checklist!D20="Yes")," All CCT representatives, CA and their delegates have been duly appointed.",IF(Checklist!D19="Yes"," All CCT representatives have been duly appointed.",IF(Checklist!D20="Yes"," The CA and their delegates have been duly appointed.","")))
did you do >Paste Special as Formulas?
Yes that was my 1st approach however it results in the following:
="The form of Contract is the "&#REF!&IF(AND(#REF!="Yes",#REF!="Yes")," All parties have access to the complete signed MoA and applicable standard specifications for this contract. ","")&IF(AND(#REF!="Yes",#REF!="Yes")," All CCT representatives, CA and their delegates have been duly appointed.",IF(#REF!="Yes"," All CCT representatives have been duly appointed.",IF(#REF!="Yes"," The CA and their delegates have been duly appointed.","")))
I believe the problem is that the original formula does not make reference to the sheet. If the formula looked like this I would be able to move it easier but manually editing the formulas will take me days.
="The form of Contract is the "&'checklist'!E17&IF(AND('checklist'!D16="Yes",'checklist'!D18="Yes")," All parties have access to the complete signed MoA and applicable standard specifications for this contract. ","")&IF(AND('checklist'!D19="Yes",'checklist'!D20="Yes")," All CCT representatives, CA and their delegates have been duly appointed.",IF('checklist'!D19="Yes"," All CCT representatives have been duly appointed.",IF('checklist'!D20="Yes"," The CA and their delegates have been duly appointed.","")))
Can you offer an easier way to include the sheet reference in the formula other than manually editing it - it is 508 Cells with complex formulas and the probability of messing it up is huge.