Exporting data to another spreadsheet based on 2 checkbox criteria

Question

I've been trying to figure this out for a while and my friend told me to ask over here! For some context, I have multiple projects where we track each invoice our sub-contractors send. Each project has its own spreadsheet. The invoice tracking sheet looks like this: Sub-Contractor Tracker Example

Once the Billed? checkbox is true, A12:D12 should populate another spreadsheet, (Sheet Y). The tricky part for me is that I'd like it to be removed from Sheet Y once the Final Verification checkbox is marked true. (So Billed? and Final Verification would both be marked true) I'm able to use SQL imports to linked sheets to get the data to Sheet Y if Billed? is true but the Final Verification second criteria is the main issue I'm running into.

I'm not too great at explaning things so please let me know if you need some more clarification.

Thanks so much!!!

Answer 1

RECOMMENDATION:

You can check this option below by using a combination of ARRAYFORMULA, IMPORTRANGE, IFERROR, IF, TRIM & QUERY functions and then place it on cell A1 of the Sheet Y spreadsheet:

=ARRAYFORMULA(IFERROR(IF(importRange("GOOGLE_SHEETS_ID", "SHEET_NAME!K12:K") = false, TRIM(QUERY(importRange("GOOGLE_SHEETS_ID", "SHEET_NAME!A12:K"), "Select Col1, Col2, Col3, Col4 WHERE Col9 = TRUE")),""),""))

Just replace the GOOGLE_SHEETS_ID with your actual Sub-Contractor Tracker spreadsheet ID. Click here if you do not know where to locate the Sheets ID. And lastly, replace SHEET_NAME with the actual sheet name on your Sub-Contractor Tracker spreadsheet.

NOTE:

I'm unable to replicate what you've done here, "I'm able to use SQL imports to linked sheets to get the data to Sheet Y if Billed? is true", so I've used QUERY(importRange("GOOGLE_SHEETS_ID", "SHEET_NAME!A12:K"), "Select Col1, Col2, Col3, Col4 WHERE Col9 = TRUE") function on my testing to populate 'Sheet Y' if Col9 or Billed? has a true value.

SAMPLE

Sub-Contractor Tracker spreadsheet w/ data on 'Sub-Contractor Tracker' sheet:

Spreadsheet Sheet Y with the combination of functions added on cell A1:

Example Scenario:

If Invoice # 1 gets verified:

Invoice # 1 gets removed on the on spreadsheet Sheet Y: