QuickBooks CSV import: "#REF!, #NAME?, #N/A or #VALUE!" errors

QuickBooks Online’s spreadsheet importer reads a CSV or Excel file cell by cell before it reads it as accounting data, and the three shapes on this page are not one problem wearing three names. A cell holding #REF! (a reference to a cell that no longer exists), #NAME? (a name the spreadsheet does not recognise), #N/A or #VALUE! is a broken-formula token rather than a value: there is nothing in it to post to an account, a date or an amount. That is why it is a blocker here — for what the cell is, not because a vendor sentence says so, which is also why the rating survives Intuit rewriting the page. A cell that still holds a formula instead of the value it produced, and a row with nothing in any column, carry real risk of a different size: the cached value a file keeps behind a formula can be stale or missing altogether, and a blank row can split one document’s rows apart. But the only Intuit sentence naming those two — “The spreadsheet doesn’t have any formulas, charts, or blank rows” — is published for a customers-and-vendors import, and on no bank-transaction or invoice page, so this page reports them as warnings rather than telling you QuickBooks will refuse the file.

Accounting Import Check runs three separate rules over every cell in your file, and rates them differently on purpose. spreadsheetError looks for the broken-formula tokens Intuit names — #REF!, #NAME?, #N/A and #VALUE! — plus five more it does not: #DIV/0!, #NULL!, #NUM!, #SPILL! and #CALC!, which are the same kind of cell and are caught for the same reason. It is a blocker, and it is a blocker for every target this tool supports, because no accounting system reads a token as a figure. formulaCell looks for any cell whose text still starts with “=”, and blankRow looks for a row with nothing in any column; both are warnings for a QuickBooks bank-transaction file, for the reason above. Run against a five-row sample file built to exercise all three, the engine reported one blocker and two warnings.

Open the file in a spreadsheet program, not a text editor — formula and error tokens are easy to miss in raw CSV. Select every column and use "Paste Special → Values Only" over itself to turn every formula into the plain number or text it produced. Search for #REF!, #NAME?, #N/A and #VALUE! (Excel’s Find & Replace, or a filter on each column) and replace each one with the correct value — there is usually no way to recover what a broken reference meant, so this often means retyping the cell from the original statement. Delete any row that is completely empty rather than leaving it as a spacer.