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?

  • What to you mean by "Something similar to this"? The link points to a question with several answers. Commented May 2, 2018 at 14:31
  • I meant a problem similar to mine.
    – Riccardo
    Commented May 2, 2018 at 14:32

2 Answers 2


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)

  • Therefore there's no way to prevent unpredictable shift of the columns in the originating sheet?
    – Riccardo
    Commented May 2, 2018 at 19:36
  • By the solely use of IMPORTRANGE, there isn't. Commented May 2, 2018 at 19:58
  • @Riccardo Maybe you should consider to improve your question to provide further details like if your source range use or not the first row for column headers. Commented May 2, 2018 at 20:03
  • 1
    Yes, basically I'd like import a whole existing sheet (including headers), excluding rows where column x has a given value. Unfortunately the column my be moved, vanishing functionality
    – Riccardo
    Commented May 2, 2018 at 20:48

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"

  • 1
    Confirmed, this works fine. The other (currently accepted) answer seems to be outdated
    – Motin
    Commented Jan 24, 2020 at 6:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.