Skip to Content
Former Member
Dec 23, 2013 at 06:38 PM

I am getting #REF in all formulas when query has no results, but they stay #REF when query revised to get results


I have a query in one tab (sheet) of the workbook; call it Q1. In second sheet, call it W1 cell A1, I have a formula that references query result; for example, =Q1!A24 (get contents of cell A24 in query Q1).

When a query is made with no results, the formula in W1:A1 becomes "#REF!"

Now, when I revise the query to get some actual results, the contents of the formula cell in W1 remains "#REF!" and it does not pick up the contents of the query result in Q1!A24.

I have also seen where the new results appear further down in the W1 report, but the cell W1:A1 remains "#REF!"

Is there a setting somewhere to solve this? A difference reference in the formula? Help! Thanks, Gary