pythonexcelstringexcel-formulaxlwings

Error in assigning formula to a column using the F-strings

I needed to replace few columns in the raw data sheet (pivot from this sheet) with formulas.
I have used Xlwings package to do this and it succeeds for other formulas, but not this particular one.

These formulas work;

#column Y: Year 1-FA   #Works perfectly
sheet_obj.range("Y2:Y"+str(max_row)).formula = f'=IF(NUMBERVALUE(C2)=1,CONCAT("Y1-",L2),L2)'
    
#column z: custom    #Works perfectly
sheet_obj.range("Z2:Z"+str(max_row)).formula = f'=CONCAT(B2,"-",E2,"-",I2,"-",J2,"-",K2)'

This following returns errors;

#column P: New Out of PP   #this is whwre the it shows error
    sheet_obj.range("P2:P"+str(max_row)).formula = f'''=IFERROR(IF(OR(O2<>"",O2<>"NULL"),IF(OR(J2="Term 1",J2="Term 33"),O2+MIN(XLOOKUP(CONCAT(N2,C2,J2,H2),Subject_Map!$G:$G,Subject_Map!$H:$H,"",0),Raw(Merged)!S2),O2),O2),O2)'''

formula I want to assign is

'=IFERROR(IF(OR(O2<>"",O2<>"NULL"),IF(OR(J2="Term 1",J2="Term 33"),O2+MIN(XLOOKUP(CONCAT(N2,C2,J2,H2),Subject_Map!$G:$G,Subject_Map!$H:$H,"",0),Raw(Merged)!S2),O2),O2),O2)'
Solution

  • I think the problem is the sheet name Raw(Merged), you need to put single quote around it, 'Raw(Merged)'!S2:

    sheet_obj.range("P2:P"+str(max_row)).formula = '''=IFERROR(IF(OR(O2<>"",O2<>"NULL"),IF(OR(J2="Term 1",J2="Term 33"),O2+MIN(XLOOKUP(CONCAT(N2,C2,J2,H2),Subject_Map!$G:$G,Subject_Map!$H:$H,"",0),'Raw(Merged)'!S2),O2),O2),O2)'''