Copy cell from on sheet to another, if other cell in row matches values

Question

What I want, is to copy a cell(s) from one sheet to another, only if another cell in the same row (different column) have/has a particular value(s) in Google Sheets.

Ideally, I would like this to be live; if I add a row in the first sheet and the criterion matches, the second sheet will update too. Here's an example:

Sheet one

Column A | Column(s)…  | Column D
=================================
Hello    |             | Yes
World!   |             | Yes
Foo      |             | Maybe
Bar      |             |

Sheet two

Column A | Column(s)…  | Column D
=================================
Hello    |             |
World!   |             |
Foo      |             |
         |             |

So in this example, my criteria is that if the row's cell in Column D equals either Yes or Maybe, that row's cell in Column A is copied into the second sheet.

There may be similar solution to the problem in Excel! – Jonathon Oates Feb 18 '15 at 13:01 — Jonathon Oates, Feb 18 '15 at 13:01

score 8 · Accepted Answer · answered Feb 18 '15 at 13:18

8

Please try:

=query('Sheet one'!A:D,"Select A where D='Yes' or D='Maybe'")

answered Feb 18 '15 at 13:18

pnuts

58,317
11
87
139

1

That should do it. Don't know why I thought of a script. Probably thought that OP wanted to remove the row on the first sheet. :-| – JPV Feb 18 '15 at 13:37
2

Thanks, pnuts ! I undeleted it. When the last line is commented out it the row won't be removed.. – JPV Feb 18 '15 at 13:51

score 1 · Answer 2 · answered Feb 18 '15 at 13:12

Paste below script in the script editor (of a google sheet that is) and save the project. Then try editing col D of sheet1 (values: 'Yes' or 'Maybe') and see if the row moves... Feel free to change the sheetnames if needed...

function onEdit(e) {
var ss = e.source,
    sheet = ss.getActiveSheet();
if (sheet.getName() !== 'Sheet1' || e.range.columnStart !== 4 || e.value !== 'Yes' && e.value !== 'Maybe') return;
e.source.getSheetByName('Sheet2')
    .appendRow(e.range.offset(0, -3, 1, 4)
        .getValues()[0]);
sheet.deleteRow(e.range.rowStart);
}

Copy cell from on sheet to another, if other cell in row matches values

2 Answers2

Linked