Problem

External references are references to a cell or range on a worksheet in another Excel workbook, or a reference to a defined name in another workbook.

ExcelWriter does not currently have the ability to parse formulas with external references. External references in formulas will cause ExcelWriter to throw an “Unable to parse formula: Error: Could not match input” error.

Solutions

Depending on how the formulas are being used, there are two workarounds to handle external references.

Solution 1: Avoid making API calls that would cause the formulas to be parsed

The ability to delay the parsing of formulas was added in ExcelWriter version 8.5.1.  Before this version, ExcelWriter parsed every formula in a worksheet automatically.  In 8.5.1 and later versions, formulas are only parsed if it is necessary. This means that as long as there are no calls in your code that would require ExcelWriter to parse the formulas, the formulas will be preserved. Calling CopySheet, or inserting or deleting sheets, columns, or rows will all cause ExcelWriter to parse the formulas.

Solution 2: Excel’s INDIRECT Function

If you need to set a formula programmatically or use methods that would cause ExcelWriter to parse the formulas with external references, Excel’s INDIRECT function can be used with the formula string passed as a parameter. For example:

wb.Worksheets[0].Cells[0,0].Formula = "+INDIRECT(\"'C:\\Temp\\[Book2.xlsx]Sheet1'!A1\")";

ExcelWriter will generate the correct ourput when the INDIRECT function is used. However, in order for the external reference in the formula to take effect, the source workbook needs to be open as well. If the source workbook is not open, the cell’s value will display “invalid cell reference error (#REF!)”

Follow

Related posts: