Google Sheets Query(Importrange) with named range?

Question

I am looking for a way to query an imported range using named range. Something similar to this, however I cannot makeit to work

Sheet ID#A has a column J whose named range is "Signed" that may contain the following character '☑'

Sheet B uses the following formula:

=query(IMPORTRANGE("https://docs.google.com/spreadsheets/d/ID#A/", "Form Responses 1!A:ZZ"),"select * where Col"&MID(CELL("address",Signed),2,1)&" = '☑' ")

The the cell containing the formul will show NAME#? and hovering the tooltip will state "Unknown range name: 'SIGNED'."

Any clue?

Answer 1

IMPORTRANGE only imports range values, it doesn't import named ranges.

NOTE: Suopose that the named range Signed is on the same spreadsheet file that your QUERY formula. It still will not work because MID(CELL("address",Signed),2,1) returns the column letter instead of a column number. Replace that formula with COLUMN(Signed)

Answer 2

IMPORTRANGE seems to work just fine for me with named ranges, for example. Note that you'll still need to grant access to pull the data from the source sheet even if it's in the same google sheet. For example this will pull in the range with name "NAMED_RANGE"

=importrange("https://docs.google.com/spreadsheets/d/<spreadsheet_id>/edit","NAMED_RANGE")