-1

Note: My question is similar Googe Spreadsheets' ImportRange function.

Is it possible in Google Spreadsheets or Microsoft Excel to import data based on cell values?

For example, can I copy contents of Sheet1 to another sheet where column B == "foo"?

Target (Sheet1):

enter image description here

The destination sheet should only copy Row 2 and 4 since those are the only ones containing "foo"

Edit:

For Google Docs, I am looking to something like this:

=ImportRange("spreadsheetkey", QUERY('sheetname'!A6:E10, "SELECT A, B = 'foo'")) however I can't get it to work.

IMB
  • 15,163
  • 19
  • 82
  • 140
  • Using worksheet functions, its unlikely (although I've not done anything with GoogleDocs) Using VBA or the GoogleDocs script it would be possible. – NickSlash Apr 11 '13 at 16:02
  • 1
    `For example, can I copy contents of Sheet1 to another sheet where column B == "foo"?` Yes you can. See this link http://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s – Siddharth Rout Apr 11 '13 at 17:44

4 Answers4

1

You can do this just how you said but instead of using a Query in the ImportRange, you use the ImportRange in the Query, so:

=QUERY((ImportRange("spreadsheetkey", "sheetname!A6:E10")), "Select columnnumbers like Col1,Col2 etc Where _Criteria for select, such as (B contains 'foo') order by which column if you want to")

Kathy
  • 11
  • 1
0

you could do something like this:

first, in an extra column (for example, column D) identify the rows / cells to be copied:

range("D" & range("A" & activesheet.rows.count).end(xlup).row).formula= "=IF(B:B=""foo"", NA(),"""")

next, use SpecialCells to select the cells in Column A, and B where Column D shows NA#

range("D1:D6").specialcells(xlcelltypeformulas, xlerrors).offset(0,-2).copy
othersheet.range("A1").pastespecial xlpasteall

that should get you started with some code to play with

of course, you can also do it without VBA, just paste the formula in, the use F5, SpecialCells dialog...

Our Man in Bananas
  • 5,809
  • 21
  • 91
  • 148
0

You need to wrap the query with the importrange inside of it like this:

=QUERY(importrange("spreadsheet key","sheetname!A6:E10"), "SELECT A where B='foo'")
Aurielle Perlmann
  • 5,323
  • 1
  • 15
  • 26
0

You can do it with FILTER function:

=FILTER(IMPORTRANGE("spreadsheet_url", "'sheet_name'!A6:E10"),
IMPORTRANGE("spreadsheet_url", "'sheet_name'!B6:B10")="foo")
vstepaniuk
  • 667
  • 6
  • 14