Formula's range error in Excel 365 & 2019 after query refresh with 0 or 1 rows

[CesarF]

I'm working with spreadsheets since supercalc era...

I'm used to rely in Excel to dynamically adjust my formulas after a query is refreshed.
Before, I just set my formulas to the beginning and the end of the empty query rows.

Then after I refresh my query, all formulas (subtotals, ranges, etc...) were automatically adjusted accordingly the new query rows.
It had been working like a charm for decades.
It worked because they used 2 rows, even if query returned 0 or 1 data rows.
Recently, I think at least in 365 & 2019, a "genius" decided to "fix" it.
Now it's returning 1 row instead 2.
Suddenly all formulas relying on the query range are messed up after an empty query or with just 1 data row
Now I've do THEIR work, and manually adjust EVERY formula and RANGE with VBA.
Now I've hundreds of issues, and a lot of angry customers.

Do you know a workaround?
Do you know who's the genius?
Can you tell them to FIX it or at least guide me to a page to report their error?

br,
Cesar.

 

[GraH]

Use Power Query.. To replace the legacy query.
Since it is loaded in a table the formula get stored in the table definition and regardless of the number of rows returned will keep on working. At least that is my experience.
Of course PQ needs to be available.

 

[CesarF]

hi @GraH, thx I'll take a look to PQ
unfortunately, not all my customers are up to date.
anyway, for the record, after a long night, I found a turnaround that may work in most cases
I modified all my formulas from one row before to one row after my empty query's rows
So after refreshing, formulas are automatically updated as should be

 

[GraH]

Maybe I overlooked something in your message. When I used this feature in the past, my formula started in the first row where I expect the query to return data. Not a row before or after.
How are your query settings defined?

 

[CesarF]

hi GraH, thx but query settings has nothing to do with my issue.
my problem is with formulas relying in query rows

I mean, when I open my workbook, my query is empty
let's say it's in cells $a$20:$k$21
that's because I use first rows ($1:$19) for titles, parameters, columns totals and an update button

I use a lot of totals, like a formula in $k$18 that could be "=subtotals(9,$k$20:$k$21)"

So let's say I set filters and pressed update button
after refreshing, query will return 0, 1 or many rows
prior versions returns at least two rows, even "empty" ones for 0, or 1 rows case
but newer versions returns just 1 row

let's say query returned 10 rows: $a$20 to $k$29, ok?
in the past, all my formulas like $*$20:$*$21 will become $*$20:$*$29

with newer versions, it only works in the case of 2 or more rows
it works as expected
but with 0 or 1 rows, formulas became $*$20:$*$20
present refresh formulas are ok, as it has only one row...

BUT, all next refreshes will be wrong (except for 0 or 1 rows case ),
because now the formula has lost the bottom row
formulas in all subsequent refreshes will be stuck to $*$20:$*$20
but only in NEWER versions of Excel
because of the number of returned rows in the 0 or 1 rows case

that's why now I'm using $*$19:$*$22 in my formulas instead
in my previous example, formula's rows will be $*$19:$*$30

actually, I'm not 100% sure if I need to set the top row to query row-1
but that's the way I tested it

I hope my problem is more clear now.
Thank

 

[GraH]

aha, your formulas are on top and not to the side. Never did that, so I'm clueless at this stage.

As a sidenote: Power Query is available for version 2010 and above: are your clients using older versions? With PQ the data is loaded in a table and your formulas can use structured references.
Prior to PQ I defined my queries inside a table. But I believe that is also not possible anymore.

EDIT: maybe you can experiment with these settings. I usually go for the third (selected) option. But it depends on the use case.

 

[CesarF]

for the record... another issue.
I haven't found the exact conditions yet, but now my names are lost (#!REF) after the empty query refresh scenario.
I really think the MS genius should be ashamed... keep up the very, very, very "good" work