import data from sheet to another

1
votes

hi I have a spreadsheet file contains a names of books at column A and the type of the books in column B. and at the same file I have another sheets each one contains a specific type of books ex: sheet 2 contains the history booksK sheet 3 contains science ..etc

my Question is : how to import the books from sheet1 (which contains all books) to the other sheets dependeing on the type name (take from sheet 1 to sheet 2 all books its type is history)

I tried this formula: =QUERY(IMPORTRANGE("sheet key","Sheet1!A:C"),(select* where COLUMN(B) contains"history")) but it doesn't work ......

3
google-sheets
If you want to 'import' from withing the same spreadsheet/workbook, you do not need importrange(). A simple query() would suffice. If you help writing that query, please share a copy with some sample data. – JPV
docs.google.com/spreadsheets/d/… – sakura
i dont understand how to use query with a condition where the values is "history" – sakura

3 Answers

0
votes

Either 

=filter(books!A:B, books!B:B = "novel")

or

=query(books!A1:B, "where B = 'novel'")

will work.

0
votes

So your data looks like this, sheet is named "books" and other sheets are named by book types:

__|            A            |     B     |
1 | Who Moved My Cheese?    | Self-help | 
2 | the hunger games        | novel     |
3 | the winner stands alone | novel     |

You can use filter() function instead of query(). Here is formula to your 'novel' sheet:

=filter(books!A:A, books!B:B = "novel")

"books!" is reference to your sheet that contains data and "novel" is the type to search for. Now you can use this formula for every other sheet by changing only search term "novel" to whatever is desired.

0
votes
=iferror(QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/[YOUR_KEY_FROM_URL]/edit","Title of Sheet You are Using!A2:I42"),"select * WHERE Col5 CONTAINS 'Towel'",1),"no values yet!")

Lets break it down a bit

IFERROR allows you to display a user friendly message if this fail because no data is returned

QUERY allows you to specify the search criteria. You can omit this if you simply want to import the range of cell regardless.

IMPORTRANGE Use the url provided, then fetch this sheet, looking at these ranges (top left, to bottom right)