Using =INDIRECT in google sheets with If statement

Question

So I'm trying to create a Google sheet for a project where every 5 years we need to contact the users , I have set up in the first sheet a column for todays date - another column for date in 5 years then a countdown in the next column (which counts down every day)

I am trying to make it move over to the next sheet once the countdown has say 1 year left on it

I am using indirect =INDIRECT("time keeper!A:E") which is fine it moves over the information however it moves it over instantly - Can I Set it up so once the timer reaches <365 days it moves over to the new sheet and then once I have contacted them I change the date to the current date then make it move back over to the original spreadsheet? or am I expecting too much here of google sheets?

Thanks in Advance!

Answer 1

You can use QUERY to select data based on certain conditions. In this instance you could :

=QUERY(Sheet1! A:D, "Select * Where D =1")

Assuming D is your helper column, if you selected up to E and E was your tick box for contacted you could set it to a true false boolean

=QUERY(Sheet1! A:E, "Select * Where D =1 AND where D = 'FALSE' ")

This would only pull people yet to be coctacted.

You could chain multiple conditions to get a more fluid approach and dynamic approach.

Hope that helps.