The issue is that openpyxl doesn’t evaluate the formula in excel. it will only return the last value saved by excel or ‘None’ (with data_only=True). The latter is what is happening when you change an input cell and use [cell].value to call the value of the cell with the formula. When you don’t change the sheet, you get the value set in excel, which is why your code works when you disable/don’t do the input to excel functionality.
Easiest way to around the issue is to use xlwings, this should work the way you intend it to. There are also a few other options. such as directly using windows excel commands to update the sheet then using openpyxl to read but I feel swapping modules is the simpler solution. You may also want to consider bringing the function to the python side of the process and just writing the result to the excel sheet.
how to get formula result in excel using xlwings
import xlwings as xw
sheet = xw.Book(r'C:/path/to/file.xlsx').sheets['sheetname']
result = sheet['X2'].value
1
solved Openpyxl is unable to read after modifying