Forum Discussion
Formula move between sheets
In my excel workbook I have two sheets called "checklist" and "Report_Text". The sheet "Checklist" range AA16:AG204 consists of a mix of text and complex formulas.
I want to move this to sheet "Report_Text" range A16:G205. The complex formulas should however still refer to sheet "Checklist" and not "Report_Text" so a simple copy paste does not work.
An example of the formula in sheet "Checklist" cell AD16 is as follows:
="The form of Contract is the "&E17&IF(AND(D16="Yes",D18="Yes")," All parties have access to the complete signed MoA and applicable standard specifications for this contract. ","")&IF(AND(D19="Yes",D20="Yes")," All CCT representatives, CA and their delegates have been duly appointed.",IF(D19="Yes"," All CCT representatives have been duly appointed.",IF(D20="Yes"," The CA and their delegates have been duly appointed.","")))
How do I achieve this move while maintaining the formulas referring to sheet "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.","")))
6 Replies
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!".
- Hello,
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.
