Function Needed - Was this person listed in the previous week?

K L

Hi @Genevieve P,

I hope you are doing great. I have two issues that are related to https://community.smartsheet.com/discussion/70615/need-formula-is-this-person-listed-again-in-the-next-week/p1?new=1.

Question: How can I get that red cell to say "Needs to be Activated"?

Needs to be activated formula:

=IF(OR(COUNTIFS(Person:Person, Person@row, Status:Status, "Active", [End Date]:[End Date], IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER([Start Date]@row - 7)) >= 1, COUNTIFS(Person:Person, Person@row, Status:Status, "Tentative", [End Date]:[End Date], IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER([Start Date]@row - 7)) >= 1, COUNTIFS(Person:Person, Person@row, Status:Status, "Hold", [End Date]:[End Date], IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER([Start Date]@row - 7)) >= 1, COUNTIFS(Person:Person, Person@row, Status:Status, "Closed", [End Date]:[End Date], IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER([Start Date]@row - 7)) >= 1, COUNTIFS(Person:Person, Person@row, [End Date]:[End Date], <[Start Date]@row, [End Date]:[End Date], >=([Start Date]@row - 7), [End Date]:[End Date], IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER([Start Date]@row)) >= 1), "Already Activated", "Needs to be Activated")

Needs to be deactivated formula:

=IF(COUNTIFS(Person:Person, Person@row, [Start Date]:[Start Date], IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER([End Date]@row + 7), Status:Status, OR(@cell = "Active", @cell = "Hold", @cell = "Tentative")) + COUNTIFS(Person:Person, Person@row, [Start Date]:[Start Date], >[Start Date]@row, [Start Date]:[Start Date], <([Start Date]@row + 7), Status:Status, OR(@cell = "Active", @cell = "Hold", @cell = "Tentative")) >= 1, "Needed next week, keep Activated", "Not needed next week, okay to Deactivate")

1) Person 1 was scheduled for 11/23-11/24.

2) After Person 1 completed their scheduled time, because they weren't scheduled in the following week, "Needs to be Deactivated" was reflecting as "Not needed next week, okay to Deactivate".

3) So the automations that were set up pushed the deactivation request and this cell was changed to "GTT Deactivated"

4) Then, last minute, Person 1 was added to the following week.

5) That caused Person 1's to say "Already Activated" when it actually needed to be activated because it had previously been deactivated.

Then a totally different issue is when a Person is listed on multiple rows for the same scheduled engagement.

The issue is that all 6 rows will have automated emails that are sent to request the activation. But I only want one of those rows to send. I also don't want to have to add a new column but I can modify the automations if you think that will help. Essentially, the rows are exactly identical. Is there a way to incorporate something into the function so one row says "Needs to be Activated" but the other 5 rows say "Already Activated"?

Then, same ask for the Deactivation function. How to get it to reflect as 1 row "Not needed next week, okay to Deactivate" and the other 5 rows "Needed next week, keep Activated"?

Thank you!

Kellie

Genevieve P.

Hi @K L

I have to admit I'm at the end of my capabilities when it comes to these formulas. I've tried adding in a number of different criteria but the COUNT formula keeps returning "Already Activated" if any other rows match the criteria (more than just the one that now says "Deactivated").

Personally, I would go back to what we discussed in your other post, and think about potentially leveraging different tools (such as Conditional Formatting and Reports) in order to highlight what's needed to change... then do pieces of this process manually, instead of trying to automate the entire sheet.

This may be a better way to go since Reports will soon be updated with Grouping & Summary features. When this is released it may be easier to filter your sheet based on dates and then group the content by user, highlighting the current Status this way.

At this point I'm not sure if it's possible to build a formula to meet everything that you need it to do, although I still have hope that someone else in the Community may have a different or complete solution for you!

Cheers,

Genevieve

Paul Newcome

I wish I had more time to dive into this. I have it on my list of things to work on, but unfortunately time has become rather scarce for me these past few months.

I also recommend making some of this process a manual process, but I will certainly let you know if I am able to come up with something.