Query refresh is altering COUNTIFS range

[eclpbarney]

I have built a dashboard sheet that references information on other sheets that are updated by refreshing queries against TFS.

At times (but not always), my dashboard cells will bang out to #VALUE! because my formula

=COUNTIFS('Sheet2'!$A$3:$A$5000,$B4,'Sheet2'!$K$3:$K$5000,F$3)

becomes

=COUNTIFS('Sheet2'!$A$3:$A$5000,$B4,'Sheet2'!$K$3:$K$5001,F$3)

it tends to be off by 1, but sometimes more. but it's always criteria range 2 that changes

 

[pbornemeier]

Perhaps your query RefreshStyle is set to insert rows, rather than overwrite them and the in-cell formula adapts to the moved last row.

RefreshStyle Property

Returns or sets the way rows on the specified worksheet are added or deleted to accommodate the number of rows in a recordset returned by a query. Read/write XlCellInsertionMode.
XlCellInsertionMode can be one of these XlCellInsertionMode constants.

xlInsertDeleteCells. Partial rows are inserted or deleted to match the exact number of rows required for the new recordset.

xlOverwriteCells. No new cells or rows are added to the worksheet. Data in surrounding cells is overwritten to accommodate any overflow.

xlInsertEntireRows. Entire rows are inserted, if necessary, to accommodate any overflow. No cells or rows are deleted from the worksheet.