Mar 13, 2020
Merging the Importrange with the IF function?
We have a drop-down list with the statuses "No" and "Posted" on column B, repeated on every cell of column B, to let us know when something has been posted or not yet.
We would like to find a way to import the data from a particular ROW onto a different sheet every time a specific selection is made on the drop-down list for that same ROW, (e.g.: when drop-down list is set to "posted"), the information is sent to the other sheet.
We've tried using the IMPORTRANGE function, however, we would need a way to connect it to an "IF" function ( i think it should be the "IF" function) so that it only sends the data when the wanted item is selected on the drop-down list ("Posted").
Anyone can please give us feedback on this problem?
Thanks in advance
Mike
Details
This question is locked and replying has been disabled.
Community content may not be verified or up-to-date. Learn more.
Last edited Mar 13, 2020
Mar 13, 2020
something like this should work
=Query(importrange("1K56PqW8lONbuuPcYzpoLTkvDkmB7ATZ0enaA8b-Fd3c","Sheet1!A1:F"),"select * where Col2 ='Posted' ",1)
Please ignore ▼ this text ▼ forum - over-ride text
this forum is NUTS - why cant i post a simple reply?
this forum is NUTS - why cant i post a simple reply?
this forum is NUTS - why cant i post a simple reply?
this forum is NUTS - why cant i post a simple reply?
this forum is NUTS - why cant i post a simple reply?
this forum is NUTS - why cant i post a simple reply?
this forum is NUTS - why cant i post a simple reply?
this forum is NUTS - why cant i post a simple reply?
Please ignore ▲ this text ▲ forum - over-ride text
Original Poster Mike James 1152 marked this as an answer
Mar 13, 2020
Hi Mike,
You might try somethign like this formula, but with your spreadsheet link instead of the words [spreadsheet link] and your tab name instead of the words [tab name].
You might try somethign like this formula, but with your spreadsheet link instead of the words [spreadsheet link] and your tab name instead of the words [tab name].
First just do a "naked" importrange so that you're prompted to allow access to the sheet to be imported, something like:
=IMPORTRANGE("[spreadsheet link]","[tab name]!A1")
Once you've allowed access, then modify the formula to be this:
=QUERY(IMPORTRANGE("[spreadsheet link]","[tab name]!A:B"),"select Co1, Col2 where Col2= 'Posted' ",1)
Note the use of the single quotes around the word Posted.
If that doesn't work for you, it might be best to share a sample sheet. Check out this link for the best way to do that.
Cheers,
Matt
Matt
Original Poster Mike James 1152 marked this as an answer
All Replies (3)
Hi Mike,
You might try somethign like this formula, but with your spreadsheet link instead of the words [spreadsheet link] and your tab name instead of the words [tab name].
You might try somethign like this formula, but with your spreadsheet link instead of the words [spreadsheet link] and your tab name instead of the words [tab name].
First just do a "naked" importrange so that you're prompted to allow access to the sheet to be imported, something like:
=IMPORTRANGE("[spreadsheet link]","[tab name]!A1")
Once you've allowed access, then modify the formula to be this:
=QUERY(IMPORTRANGE("[spreadsheet link]","[tab name]!A:B"),"select Co1, Col2 where Col2= 'Posted' ",1)
Note the use of the single quotes around the word Posted.
If that doesn't work for you, it might be best to share a sample sheet. Check out this link for the best way to do that.
Cheers,
Matt
Matt
Original Poster Mike James 1152 marked this as an answer
something like this should work
=Query(importrange("1K56PqW8lONbuuPcYzpoLTkvDkmB7ATZ0enaA8b-Fd3c","Sheet1!A1:F"),"select * where Col2 ='Posted' ",1)
Please ignore ▼ this text ▼ forum - over-ride text
this forum is NUTS - why cant i post a simple reply?
this forum is NUTS - why cant i post a simple reply?
this forum is NUTS - why cant i post a simple reply?
this forum is NUTS - why cant i post a simple reply?
this forum is NUTS - why cant i post a simple reply?
this forum is NUTS - why cant i post a simple reply?
this forum is NUTS - why cant i post a simple reply?
this forum is NUTS - why cant i post a simple reply?
Please ignore ▲ this text ▲ forum - over-ride text
Original Poster Mike James 1152 marked this as an answer
Mar 14, 2020
It worked indeed, found a few threads online and the one that I finally was able to use was:
=QUERY(IMPORTRANGE("URL","tabname!A2:R100"),"where Col2='posted'")
=QUERY(IMPORTRANGE("URL","tabname!A2:R100"),"where Col2='posted'")
It seems can do it without the select.
I've tried both your formulas as well and it works, thank you so much for your feedback !
I struggled in the beginning because I had a few columns merged, haha. Now I know .
I struggled in the beginning because I had a few columns merged, haha. Now I know .
Thanks again ! :)