Circular Formulas Result in #Value! Error on First Load in Excel
I'm automating a fairly complex Excel workbook and kept running into an issue where, when I connect a circular formula, the worksheet opens with ugly #Value! errors in the cells that are in the circular formula chain. If I click into one of the cells and hit enter, however, the #value! errors go away immediately. I've traced my workbook again and again to see if I made a mistake somewhere in my formulas. I also went through and formatted the number_format of every cell in my openpyxl code to try to ensure no strings are accidentally referenced. Still getting the same error. I have automatic calculations and iterative calculations turned on in Excel.
Finally, I thought I'd try to construct a very simple circular formula using Openpyxl, and, low and behold, it also has the same problem.
Using this code, the resulting workbook exhibits the behavior I'm seeing:
from openpyxl import Workbook
wb = Workbook()
# grab the active worksheet
ws = wb.active
# Data can be assigned directly to cells
ws['A1'] = 'A'
ws['A2'] = 100000
ws['B1'] = 'B'
ws['B2'] = .12
ws['C1'] = 'C'
ws['C2'] = 0
ws['D1'] = 'D'
ws['D2'] = '=A2+C2'
ws['C2'] = '=ROUND(B2*D2,0)'
wb.save("sample.xlsx")
It almost seems like what's happening is (in the example above), cell C2 is trying to resolve before cell D2 and, unless you click into D2 and hit enter, Excel can't figure out how to resolve this. Is this an Openpyxl issue? Anyone have any ideas on how to work around this?