Need Formula: "Is this person listed again in the next week?"

Genevieve P.

In regards to the Quarter, we could use cross-sheet formulas but that will get pretty tricky. The easiest thing to do in this scenario would be to use a Report with two source sheets during the cross-over time, and manually check/update for those two weeks.

Paul Newcome

If I may ask a few questions based on the thread and the below screenshot provided on 9/16/20...

How exactly is it determined when a license needs to be activated and for how long? It looks like it is always deactivated on the end date, but I see a few rows (the ones in green) where the start date does not match up to the date listed in your notes column that it will be activated on?

Are licenses always activated on a specific day of each week?

Why is it that persons 2, 6, and 12 in the green section are scheduled to be deactivated on the end date, but the others in that section don't have notes of being deactivated on a specific date? Is it because they aren't listed for the following week?

Does a license just automatically carry over to the next week if they are listed? So if I was listed for 4 weeks in a row, ten I would have an "Already Activated for all 4 entries (once "Activated" was entered into my first row in the [Activated Column] column)?

How are new entries added? Would you be open to a slight modification of your layout if needed, or do we need to stick with your current layout?

And did I read that you are working each sheet quarterly?

K L

Hi @Genevieve P and @Paul Newcome Thanks to both of you for your continued help. I'll try and give more context.

I like your idea about conditional formatting to highlight the mismatch that a row might get changed to "Deactivated" but "yes" in the next week. And then I'll also create a report to view cross over between quarters and manually change the last week and first week of each quarter. Great call out.

To give more context, the screenshot I shared on 9/16 is showing a test page copy of only the rows I'm working on that are part of a huge schedule that's separated into master sheets by quarter. So there's many more columns and rows not included for other unrelated things going on.

Q - Are licenses always activated on a specific day of each week?

A - I have an automation set up to send an update request email to request the license activation every Thursday at 8am ET for any with a start date in the next 8 days. The license approval team (sent to an alias with multiple people on it) is pushed an update request automated email and once the license is activated, they respond in the automated webform "Activated". That updates SS in the "Activated Column". They don't have full SS view or edit access to the master sheets so this allows them to get the request for an exact class and person and respond via the update request form. So that's why you'll see 9/17 as the activated date on those green rows. But then, deactivation will be a quicker turn around and the automated email will be sent on the end date at 5PM PT. So that's why you see various dates for the deactivation.

The issue I see is that it also counts anything on Friday, which should have been requested in the previous week. So if I push the activation email on 9/17, but there's a date scheduled for 9/18, it will count that too and send or not send an email for that person. But I can't set up a moving range for an automation filter that I can see. Otherwise I'd say, send for anything with a start date in the next Monday through Friday.

Q - Why is it that persons 2, 6, and 12 in the green section are scheduled to be deactivated on the end date, but the others in that section don't have notes of being deactivated on a specific date? Is it because they aren't listed for the following week?

A - That is because column "Do they have one next week? No=Deactivate" says yes. So that means the person on that row is scheduled in the following week and so we'll need to keep their already activated license activated, and not deactivate it.

Q - Does a license just automatically carry over to the next week if they are listed? So if I was listed for 4 weeks in a row, ten I would have an "Already Activated for all 4 entries (once "Activated" was entered into my first row in the [Activated Column] column)?

A - Yes, we'll keep the same license for that person activated as long as they have an upcoming date scheduled.

Q - How are new entries added? Would you be open to a slight modification of your layout if needed, or do we need to stick with your current layout?

A - New rows are managed in the master sheet by a separate team. There's a placeholder row for each week of the year and then a date and person is added in. Open to ideas but we might be restricted depending on the impact to the master sheet.

Q - And did I read that you are working each sheet quarterly?

A - Yes, so 4 master sheets per year. For this particular ask, I'll have various numbers of people in each quarter who will need licenses. They meet other criteria which I set in the automation filters.

K L

@Genevieve P I've run into an issue where the two functions we have created are counting cancelled various cancelled rows that are being retained in the schedule, so it's giving opposite answers. I'm wondering how to add more filter parameters into the functions we have.

With the Yes/No function:

=IF(COUNTIFS([Person]:[Person], [Person]@row, [Start Date]:[Start Date], IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER([End Date]@row + 7)) + COUNTIFS([Person]:[Person], [Person]@row, [Start Date]:[Start Date], >[End Date]@row, [Start Date]:[Start Date], <([End Date]@row + 7)) >= 1, "Yes", "No")

Can this include "If the Status column of the people being counted is "active", "hold", tentative"?

With the Already Activated/Needs to be Activated function:

=IF(OR(COUNTIFS([Person]:[Person], [Person]@row, [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")

Can this include "If the Status column of the person being counted is "active", "hold", tentative"?

So basically I don't want to look at rows with Status column "cancelled", "-", or "blank" for either function. I thought I could filter with the automations but the way I have it set up is to trigger or not based on what the cell is reflecting. Thanks for your help!

K L

@Genevieve P I think I have the right formula for the Already Activated/Needs to be Activated.

=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, [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")

But I can't figure out how to make the Deactivate "Yes/No" function only count rows where the Status Column says "Active or Tentative or Hold or Closed".

This is the last function we have that doesn't exclude or include any statuses:

=IF(COUNTIFS([Person]:[Person], [Person]@row, [Start Date]:[Start Date], IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER([End Date]@row + 7)) + COUNTIFS([Person]:[Person], [Person]@row, [Start Date]:[Start Date], >[End Date]@row, [Start Date]:[Start Date], <([End Date]@row + 7)) >= 1, "Yes", "No")

Can you help me with it please? I only want it to say yes if the person on this row has an upcoming date and the status on that upcoming date is active/tentative/hold/closed.

Thanks!

Kellie

K L

@Genevieve P @Paul Newcome Are either of you able to continue to help me? Just wondering if you’re still around to assist and I appreciate your continued help. Thank you!

Paul Newcome

@K L Sorry about that. I have the thread saved, but my workload unexpectedly quadrupled lately and I haven't had much time to "play around" in Smartsheet with anything other than work related stuff. I haven't forgotten about you. I just haven't had the time. Sorry about that.

Genevieve P.

Hi @K L Sorry about the delay!

I have to admit that I'm starting to get a little confused by all of the logic. I'll work on this now and see if I can come up with something for that second formula.

Genevieve P.

Hi @K L

I think Paul's idea of potentially changing around your process may be the best way to move forward, as I'm at a bit of a roadblock trying to think of more formulas to help.

I have a few additional questions:

• Do you know exactly who is going to be needing licenses each quarter? (Will the users stay the same list of people?)

I'm wondering if we could instead create just one sheet, with only one row per-person.

Having one row per-person could potentially simplify this process quite a bit. You could still send alerts out when certain dates are reached or request updates for the activation date or status, etc.

• Do you need to see the historical data for each week?

If you only had one row per-person, you could use a copy-row automation to store historical data when changes are made (if needed) in a separate, "history", sheet.

• How are the deactivations made?

Is that an automatic thing that happens at a certain time, or is this a manual adjustment that a team makes based on the end date put into the sheet?

For example, you said that you'd "keep" the same license activated. Does that require additional action, or is that automatic and the deactivation requires action?

K L

Hi @Genevieve P ! Thanks for connecting back.

The master sheets I'm working in have a lot of other dates and teams involved, so I can't move the rows I want to a separate master. My screenshots to you are only a small section of what I'm working on. I'm using filter settings on the automations to pick out the rows that I want to send email requests for.

Formula that I currently have entered into "Do they have one next week? No=Deactivate" column:

=IF(COUNTIFS(Person:Person, Person@row, [Start Date]:[Start Date], IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER([End Date]@row + 7)) + COUNTIFS(Person:Person, Person@row, [Start Date]:[Start Date], >[End Date]@row, [Start Date]:[Start Date], <([End Date]@row + 7)) >= 1, "Yes", "No")

So the formula I'm using is reading canceled, - and rescheduled rows, and giving me false "yes" answers. I only want it to say yes if the status is active, hold, or tentative but I can't figure out how to make that happen with the formula you've helped me create. If it's says no, then a deactivation request will be sent with an automation I have set up.

Thanks so much!

Kellie

Genevieve P.

Hi @K L

To add in criteria for the Status Column, you can reference the whole column, then use the OR function to specify the different criteria you're looking for.

Try this:

=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, "Yes", "No")

Let me know if you're seeing incorrect results with this!

Cheers,

Genevieve

K L

Hi @Genevieve P . Oh my, I think that did it! Thank you so much for your help. You are amazing!

Genevieve P.

@K L

I'm so glad that worked!!! Let us know if you're seeing anything else in the logic you want adjusted. You may want to start a new Community post/thread if you have additional questions if it's a new topic, in case other members have ideas to help.

Cheers!

Genevieve