I'm using openpyxl to read an Excel spreadsheet with a lot of formulas. For some cells, if I access the cell's value as e.g. sheet['M30'].value I get the formula as intended, like '=IFERROR(VLOOKUP(A29, other_wksheet, 9, FALSE)*E29, "")'. But strangely, if I try to access another cell's value, e.g. sheet['M31'].value all I get is =, even though in Excel that cell as essentially the same formula as M30: '=IFERROR(VLOOKUP(A30, other_wksheet, 9, FALSE)*E29, "")'.
This is happening in a bunch of other sheets with a bunch of other formulas and I can't seem to find any rhyme or reason for it. I've looked through the docs and I'm not loading data_only=True so I'm not sure what's going wrong.
This sounds very much like you are looking at cells using "shared formulae". When this is the case the same formula is used by several cells. The formula itself is only stored with one of those cells and all others are marked as formulae but just contain a reference. Until version 2.3 of openpyxl all such cells would return "=" as their value. However, version 2.3 now performs the necessary transformation of the formula for dependent cells. ie. a shared formula of say "=A1+1" for A1 will be translated to "=B1+1" for B1.
Please upgrade to 2.3 if you are not already using it.
If this is not the case then please submit a bug report with the sample file.