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. – Rubén May 2 '18 at 14:31
  • I meant a problem similar to mine. – Riccardo May 2 '18 at 14:32

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)

| improve this answer | |
  • Therefore there's no way to prevent unpredictable shift of the columns in the originating sheet? – Riccardo May 2 '18 at 19:36
  • By the solely use of IMPORTRANGE, there isn't. – Rubén May 2 '18 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. – Rubén May 2 '18 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 May 2 '18 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"

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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