I am importing ranges from other spreadsheets and filtering them using QUERY.
But I am trying to make my QUERY more resilient to changes, such as addition or removal of columns, as it can break when new columns are added or old removed. Instead of referencing the column by its number (e.g. Col1, Col2, etc.), I would like to reference it either by the header or by the named range (preferred).
Essentially, instead of referencing, for example, the Column 18 (R) as "Col18", I would reference it in a way that won't break if any other columns are removed/added. Which, I figured, could be done by making the column a named range and referencing it as such in QUERY.
So, instead of:
=QUERY({IMPORTRANGE("https://docs.google.com/spreadsheets/d/..."; "Sheet1!A:W")}; "Select Col1 where Col18 is not null";0)
It would be:
=QUERY({IMPORTRANGE("https://docs.google.com/spreadsheets/d/..."; "Sheet1!A:W")}; "Select Col1 where Col"&COLUMN(namedRange)&" is not null";0)
This, unfortunately, does not work. Despite supposedly working according to the answer here.
The error is
Unknown range name: 'namedRange'.
The same error appears if I try something like:
=QUERY({IMPORTRANGE("https://docs.google.com/spreadsheets/d/..."; "Sheet1!A:W")}; "Select Col1 where "&namedRange&" is not null";0)
As part of testing, the only way I managed to get this to "work" is if the imported range (and thus named range) are in the same spreadsheet that they are being imported in, but that will not work for me (since I am importing ranges from other spreadsheets) and also beats the purpose of using IMPORTRANGE. It seems like the "COLUMN" formula does not see the named range when used with IMPORTRANGE from another Spreadsheet.
Am I doing something wrong, or is there an alternative way of doing this?
Could you please try adding a new IMPORTRANGE inside the Column function?:
=QUERY({IMPORTRANGE("https://docs.google.com/spreadsheets/d/..."; "Sheet1!A:W")}; "Select Col1 where Col"&COLUMN(IMPORTRANGE ("https://docs.google.com/spreadsheets/d/.."),namedRange))&" is not null";0)
If this doesn't work, then I think you should try with the headers. Then you could use Match to find the column numbers:
=QUERY({IMPORTRANGE("https://docs.google.com/spreadsheets/d/..."; "Sheet1!A:W")}; "Select Col1 where Col"&MATCH("HeaderExample",IMPORTRANGE("https://docs.google.com/spreadsheets/d/..."; "Sheet1!A1:W1"),0)&" is not null";0)
Or, in order not to repeat the IMPORTRANGE:
=LET(imp,IMPORTRANGE("https://docs.google.com/spreadsheets/d/..."; "Sheet1!A:W"),
QUERY({imp}; "Select Col1 where Col"&MATCH("HeaderExample", INDEX (imp,1),0)&" is not null";0))
Combination of both methods: Finding first value of named range in order to match it with the headers
=LET(imp,IMPORTRANGE("https://docs.google.com/spreadsheets/d/..."; "Sheet1!A:W"),
QUERY({imp}; "Select Col1 where Col"&MATCH(INDEX(IMPORTRANGE("https://docs.google.com/spreadsheets/d/..."; namedRange),1), INDEX (imp,1),0)&" is not null";0))