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.","")))
TiaanvC Obviously, AI had it wrong again. You would need to apply the find & replace to the formulas before you copy them and get #REF! errors. But it's may still be a lot of manual work, depending on how similar the formulas are in the entire range.
For instance, you could replace each occurrence of &E with &checklist!. And then replace all occurrences of (D and ,D with (checklist! and ,checklist! respectively. That would fix the formula you mentioned in your initial question. Note that you need to include the ampersand, the parenthesis and the comma. Otherwise, all occurrences of "E" and "D" anywhere in the formula will be replaced with "checklist!".
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.","")))