CODEWITHSUNDEEP
google-sheets
chuck
chuck

Reputation: 369

Using =INDIRECT in google sheets with If statement

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!

Upvotes: 1

Views: 706

Answers (2)

GusTheAnt
GusTheAnt

Reputation: 33

Old question but here is another option. I would love to hear if there's an advantage of using one of these over the other.

I'm going to assume the countdown number is in C1, and the name of the original sheet is in D1, and the name of the new sheet is in E1.

=IF(C1<=365,INDIRECT(E1&"A:E"),INDIRECT(D1&"!A:E"))

This will display columns A:E from new sheet once C1 is less than or equal to 365. Otherwise it displays columns A:E from the old sheet.

So now you can modify the dates in A1 and B1, and then update the sheet names in D1 and E1 if needed and this formula will still work.

Upvotes: 1

Umar.H
Umar.H

Reputation: 23099

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.

Upvotes: 1

Related Questions