Google Sheets QUERY function - can it be written to not break when columns are inserted?

Question

So I have a query in Google Sheets

=QUERY(Transactions!C$6:F, "select C,D*-1,E,F where C > date '1990-01-01'",0)

My problem is that whenever I insert a column to the left of C in the Transactions sheet, the data range in the first parameter of the function does correctly get updated to Transactions!D$6:G, but the query does not automatically update. It is still trying to pull data from column C which is no longer included in my data range and now contains data that I do not want returned by the query.

Is there any way to use the QUERY function and write a query that accesses columns relative to the data range instead of absolute columns on the sheet? Or, if not that, is there any other way I can write the query so that it still functions when I insert a new column on the left without having to rewrite the query every time I do so?

Answer 1

=QUERY({Transactions!C$6:F}, "select Col1,Col2*-1,Col3,Col4 where Col1 > date '1990-01-01'",0) 

• Array the input {}
• Call by Column numbers Col1