Issues with QueryTables in Excel 2019

%3CLINGO-SUB%20id%3D%22lingo-sub-1438929%22%20slang%3D%22en-US%22%3EIssues%20with%20QueryTables%20in%20Excel%202019%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1438929%22%20slang%3D%22en-US%22%3E%3CP%3EPrior%20to%20Excel%202019%2C%20querytables%20returned%20at%20least%202%20rows%2C%20even%20with%20empty%20recordsets.%3C%2FP%3E%3CP%3EThis%20way%20subtotals%20and%20other%20formulas%20that%20depend%20on%20the%20dynamic%20number%20of%20query%20rows%20are%20automatically%20adjusted%20to%20the%20rows%20count%20after%20a%20refresh.%3CBR%20%2F%3ENow%20it's%20returning%20just%20one%20row%20and%20after%20that%2C%20I'm%20getting%20%23REF!%20errors%20instead%20because%20all%20formulas%20lost%20the%20query%20area.%3CBR%20%2F%3EI've%20100%2B%20reports%20working%20in%20hundreds%20of%20customers%2C%20being%20updated%26nbsp%3Bsince%20Excel%20XP%20that%20isn't%20working%20because%20of%20this%20%22upgrade%22...%20if%20it's%20not%20broken%2C%20don't%20fix%20it!%3CBR%20%2F%3EMS%20should%20return%20to%20their%20previous%20working%20scheme%20of%20two%20rows%20minimum%20and%20better%20yet%2C%20add%20a%20REAL%20row%20count%2C%20instead%20qt.resultrange.rows.count%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1438929%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1440203%22%20slang%3D%22en-US%22%3ERe%3A%20Issues%20with%20QueryTables%20in%20Excel%202019%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1440203%22%20slang%3D%22en-US%22%3ECan%20you%20perhaps%20post%20an%20example%20where%20-using%20the%20same%20dataset-%20we%20might%20see%20the%20difference%20between%20how%20an%20older%20version%20of%20Excel%20worked%20and%20how%202019%20works%3F%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Prior to Excel 2019, querytables returned at least 2 rows, even with empty recordsets.

This way subtotals and other formulas that depend on the dynamic number of query rows are automatically adjusted to the rows count after a refresh.
Now it's returning just one row and after that, I'm getting #REF! errors instead because all formulas lost the query area.
I've 100+ reports working in hundreds of customers, being updated since Excel XP that isn't working because of this "upgrade"... if it's not broken, don't fix it!
MS should return to their previous working scheme of two rows minimum and better yet, add a REAL row count, instead qt.resultrange.rows.count

1 Reply
Highlighted
Can you perhaps post an example where -using the same dataset- we might see the difference between how an older version of Excel worked and how 2019 works?