IMPORTRANGE with condition

Question

Using Google Sheets I want, within the same document, to import data from one sheet to another using IMPORTRANGE with conditions.

I have tried unsuccessfully:

=IF(IMPORTRANGE("https:URL","Inc Database!B2:B300")="permanent",IMPORTRANGE("htps://URL","Inc Database!A2:A300"),"")

and

=QUERY(IMPORTRANGE("https:/URL", "Inc Database!A2:A300"),"SELECT Col1 WHERE Col1 <> 'permanent'")

and

=FILTER(IMPORTRANGE("URL","Inc Database!A1:A250"),IMPORTRANGE("URL","Inc Database!B1:B250"="venture permanent"))

I want the function to say: Import any values from range A that meet criterion "permanent" in range B.

    A    |  B    
_________|_________
 Name    |type 
---------|-------
 Henry   |Permanent
 William |Intern
 John    |Permanent  

Solution 1

I have put a few examples in the following spreadsheet:

e.g. =QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1LX7JfbGvgBTfmXsYZz0u5J7K0O6oHJSnBowKQJGa9lY/edit#gid=0", "Inc Database!A2:B300"),"SELECT Col1 WHERE not(Col2 = 'Permanent') ")

Solution 2

You need a single quote around the reference to the sheet/tab since there is a space in the name. Using your example:

IMPORTRANGE("https:/URL", "'Inc Database'!A2:A300")

But this will only import column A, so you cannot check against column B

Then use the Query. If you want everything where B is 'Permanent' then you want (untested):

=QUERY(IMPORTRANGE("https:/URL", "'Inc Database'!A2:B"),"SELECT Col1 WHERE Col1 = 'Permanent'")

This will:

• Import all of the rows, starting at A2 from the main data sheet to use in the Query().
• Via Query, return only those where Col2 (B) contains 'Permanent'

Comments

[user7220901]

Not working, thks though. In the past, it works with space and no single quote as well. SO i do not think that is the issue.

[user7220901]

Thanks a lot, it does work. However, only one name is loaded whereas 3 meet the criteria. When i expand the formula CTRL + D, only the same name appears. Any solutions for that as well? I used this formula: =FILTER(IMPORTRANGE("docs.google.com/spreadsheets/d/…", "Inc Database!A2:A300"),REGEXMATCH(LOWER(IMPORTRANGE("docs.google‌​.com/spreadsheets/d/‌​…", "Inc Database!B2:B300")),"permanent"))

[David Tew]

I am confused as to what you exactly want to achieve. (I've made the demo ss editable now, so if you want to add some names). The yellow columns are showing those names where Type is Permanent. The others showing where Type is intern (or Not Permanent). Could you explain in a different way what I haven't understood?

[user7220901]

I added comment on the sheet., Thks

[David Tew]

I added a comment to your comment on the sheet.

[user7220901]

Thks, so did I again

[David Tew]

More comments on sheet

[user7220901]

Perfect, it works, thank you again for your patience and explanations