Need to pull rows from one tab to another tab in same spreadsheet that meet certain criteria

Question

I have a spreadsheet in Google Sheets on Mac. I would like to create new tabs in the same spreadsheet that include the rows that meet a certain criteria from a single column, which I will call the "decision column". The single decision column will contain either "Rejected" or "Complete" or be blank if the row is still in progress. One new tab would be "Rejected" one new tab would be "Complete" and one tab would be "In Progress" i.e. the cell in the "decision column" is blank for the particular row.

Ideally, it would be great if this formula could remove the complete and rejected rows out of the Master tab, and place them in another tab (In order to keep them as a record.) However, I feel this is beyond the capabilities of Google Sheets.

Once the three new tabs are set up, the idea of removing the finished rows could be accomplished with a simple "Conditional formatting" that colors the entire finished rows in the "master sheet" black (after copying it to another sheet for record keeping).

The goal here is to only have what is being worked on / the rows that are in-progress visible in the master sheet.

I have tried to use the IMPORTRANGE function, and explored the FILTER function, however I don't know how to use that to make it only copy over certain rows. This needs to happen continuously and automatically (a time delay is fine).

Answer 1

Copy and paste is tough, but you could probably do it in Apps Script. However, displaying the rows in the new tabs is absolutely the purview of the Filter function.

=Filter('Master Spreadsheet'!A:N,N:N="Rejected")

Replace the data there with your tab names, columns, and criteria.

Answer 2

Short answer

It's not possible to use Google Sheets built-in functions to copy data, but Google Sheets could be extended by using add-ons and Google Apps Script

Explanation

Google Sheets built-in functions can only calculate data. You could manually copy and paste those values but for to do that automatically it's required something else. You could explore the available add-ons in order to find one that fit your needs or use Google Apps Script but it requires programming knowledge.

There are a lot of examples in this site that could help you. Start by taking a look to linked questions in the related section on the right panel of this page.

References

Use add-ons and Apps Script Extending Google Sheets