Create Auto-Assign Formula Based on Meeting Attendance

Rach_Elle

I know Smartsheet doesn't have a 'random picker' function, so I'm trying to create one using some logic. Each department meets weekly and I'm trying to get a (seemingly random) employee name to appear as the person who is responsible for kicking off their respective meeting. My idea so far is to have it be based on the day of the month. Each employee would have however many designated days 1-31 assigned to them and then whatever day of the month it is… that's the employee's name that will appear. However, I can't figure out how to account for absentees. What if the person assigned to the meeting 'date' isn't present for the meeting? I've tried just using a basic automation workflow, but it's going to become difficult when there are 10+ people in a department to build in all of the possibilities of IF/THENs. Can anyone think of a solution using formulas to auto-populate a name?

David Tutwiler

• **** EARLY WARNING ON THIS POST ****

• I am not a mathematician and don't claim to be one. There is not a random number generator in Smartsheet, and therefore anything you attempt to create will not be truly random. I just want to make that clear up front.

First, the helper columns are [Names, Assigned Number (formula), and Present]. The rest of the columns will have formulas driving them. The Names column should be a text column where you put in the names of the people invited to the meeting. The Present column should be a checkbox column that you can use to check who was/is present in the meeting.

The Assigned Number is a formula that will assign a number to the name based on whether they were present or not. You'll notice that there is a blank row at the top starting the numbering off with a 0. That is to catch the case that Alison wasn't present, so it will skip that person and move on to the first person who was present to give them the value of 1. Here is a screenshot to help explain what I mean. If Alison, Brock, and Charlie weren't present, then David would get the value of 1 as the first person present.

The formula to use should be copied to Row 2, then drug down through the remaining rows where there is a corresponding name. That will auto-update the formula to check the row above it for the next number. For example, the formula I paste below will have [Assigned Number]1 to look at the first row (the one with a 0 in it). Dragging it down to Row 3 will update the [Assigned Number] to 2 so it looks at the previous row, and so on.

=IF(Present@row = true, [Assigned Number]1 + 1, [Assigned Number]1)

The Number of People Present column simply has a single formula in a cell to be used as a helper formula in a future formula. It is a simple COUNTIF to see how many of the checkboxes are checked. It would be written as (which reads - "How many of the checkboxes in my range are checked?"):

=COUNTIF(Present2:Present11, true)

Finally, we get to the problem column, the random selection. I will post the formula then go into its components:

=IF(ROUND(MOD((DAY(TODAY()) / 1759) * YEAR(TODAY()), [Number of People Present]2), 0) = 0, 1, ROUND(MOD((DAY(TODAY()) / 1759) * YEAR(TODAY()), [Number of People Present]2), 0))

The first formula we see is IF. This is to take care of the edge case where the formula returns a 0. The IF wraps everything in an "IF your result is a 0, make it a 1" formula. Then we get to the ROUND and MOD functions. ROUND just makes sure we have a single digit as a number. The MOD is really what we're after. The random function takes the day of the week in numbers (today is the 22nd so it would return a 22) and divides it by a large prime number, 1759. Then it takes that number and multiplies it by the year (here, 2024). Finally, it MODs (a special math operation that divides and takes the remainder) that number with the number of people present ([Number of People Present]2).

When testing this out, it does appear to generate a random-ish number within the range of the number of people you had in your meeting. I hope this helps you in your effort to randomize presenters.

Rach_Elle

Thank you for responding so quickly! My concern with this is if the absentees are not consecutive. If Alison is present, she gets a '1', then let's say that Brock is absent so his number reads as '0'. Then wouldn't Charlie's number also return a '1'? Is there a formula that will skip past the previous zeroes in the roll call and continue the count?

David Tutwiler

I am so sorry. I did not include the formula for the Assigned Number column. It prevents that issue. I'll post it here but also in the original post. For this to work, though, the first row will need to be 0 and this formula will need to be dragged down into every other row. Dragging down on the bottom right of the cell will copy the formula into every row, but update the cell number it is looking at. For instance, if you start on Row 2, the [Assigned Number] will point to row 1. If you drag that to Row 3 the [Assigned Number] will be a 2 and so on.

=IF(Present@row = true, [Assigned Number]1 + 1, [Assigned Number]1)

Rach_Elle

Thank you for all of your help! This appears to be working :)

David Tutwiler

Awesome, glad to hear it