My method of using my DAY variable into a string formula is not working properly. This method of using a variable in SQL queries and file paths a massive amount of times, however I can't figure out why its not now.
SOLVED by encasing the string in """ --- """
date = '09-13'
DAY = int(date[3:])
templateFormula = f'=SUMPRODUCT(SUMIF(INDIRECT("'"&'Ref Sheet'!$A$1:$A${DAY}&"'!$E$2:$E$150"),$D2,INDIRECT("'"&'Ref Sheet'!$A$1:$A${DAY}&"'!$K$2:$K$150")))'
print(templateFormula)
expected result:
=SUMPRODUCT(SUMIF(INDIRECT("'"&'Ref Sheet'!$A$1:$A$13&"'!$E$2:$E$150"),$D2,INDIRECT("'"&'Ref Sheet'!$A$1:$A$13&"'!$K$2:$K$150")))
Actual Result
=SUMPRODUCT(SUMIF(INDIRECT("'"&'Ref Sheet'!$A$1:$A${DAY}&"'!$E$2:$E$150"),$D2,INDIRECT("'"&'Ref Sheet'!$A$1:$A${DAY}&"'!$K$2:$K$150")))
Apply formula in this manner
for t, CELLObj in enumerate(WS['E'], 1):
if t > 1:
CELLObj.value = '=SUMPRODUCT(SUMIF(INDIRECT("'"&'Ref Sheet'!$A$1:$A${DAY}&"'!$E$2:$E$150"),$D{0},INDIRECT("'"&'Ref Sheet'!$A$1:$A${DAY}&"'!$K$2:$K$150")))'.format(t)
CodePudding user response:
try using format
on that string instead:
date = '09-13'
DAY = int(day[3:])
templateFormula = """=SUMPRODUCT(SUMIF(INDIRECT("'"&'Ref Sheet'!$A$1:$A${DAY}&"'!$E$2:$E$150"),$D2,INDIRECT("'"&'Ref Sheet'!$A$1:$A${DAY}&"'!$K$2:$K$150")))""".format(DAY=DAY)