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.","")))
If the original formula does not include explicit sheet references, and you want to quickly modify it to refer to the "Checklist" sheet, you can use the following approach:
- Use Find and Replace:
- Select the range where you pasted the formulas on the "Report_Text" sheet (A16:G205).
- Press Ctrl + H to open the Find and Replace dialog.
- In the "Find what" field, enter #REF! (replace #REF with the actual cell reference).
- In the "Replace with" field, enter 'Checklist'!.
- Click "Replace All."
This will add the sheet reference to all instances of #REF! in your selected range.
- Update External References:
- Manually verify that all external references in your formulas are correctly pointing to the "Checklist" sheet. If you encounter issues, you may need to adjust them manually.
- Verify and Test:
- Double-check the formulas to ensure they are now referring to the "Checklist" sheet.
- Test the formulas to make sure they are producing the expected results.
This method should help you quickly add the sheet reference to the formulas without manually editing each one. However, it's important to carefully review the updated formulas to ensure accuracy. The text was revised with the AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
Thanks for the response.
After copy and paste the actual cell references are missing
="The form of Contract is the "&#REF!&IF(AND(#REF!="Yes",#REF!="Yes"),....
Using he Find and Replace dialog will successfully replace #REF! with 'Checklist'! but is will not solve the problem.
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.","")))
