Data refresh breaking formulas

[tfmeier]

I have an Excel workbook with various sheets. 2 sheets are of importance for this issue: 'Extract' and 'SalesExportTable'. The 'SalesExportTable' gets refreshed via a PowerQuery that pulls data from a csv file.

'Extract' makes formula references to sheet 'SalesExportTable'.

The issue I'm getting is some formulas in 'Extract' that make reference to 'SalesExportTable' get broken when the data gets refreshed.

The easy fix is to rebuild the formulas in 'Extract' but I'd like to fix this permanently. Why do these formulas break?

[ArviLaanemets]

Have you checked the existence of data in SalesExportTable for rows with errors in Extract table. Depending on formula, missing the searched entry at all, or getting Null for returned value, can give an error as result.

[tfmeier]

The error is in sheet 'Extract'. Are you suggesting that if the source data (i.e. 'SalesExportTable') has issues such as blank or other the formula in 'Extract' may produce an error?

If so how would we than explain the issue goes away if I rebuild the formulas?