Forum Discussion

TiaanvC's avatar
TiaanvC
Copper Contributor
Jan 15, 2024
Solved

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 "Rep...
excel
Formulas and Functions
  • TiaanvC's avatar
    TiaanvC
    Jan 16, 2024

    Riny_van_Eekelen 

    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.","")))

Rodrigo_'s avatar
Rodrigo_
Steel Contributor
Jan 15, 2024
Hello,
did you do >Paste Special as Formulas?
TiaanvC's avatar
TiaanvC
Copper Contributor
Jan 15, 2024

Rodrigo_ 

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.