Formula Question

Hclarke

I'm not sure if this is an option but I am trying to create a sheet that is going to track staff visiting sites. This will need to be on a rotation. Example: Sally visits one site and the next time that site visit comes up the next staff member would be scheduled to visit. The goal would be to have the staff equally visits all the sites. Is there a formula that could make this happen?

Ric T

Hi @Hclarke,

I've seen an answer in the community that may be helpful to you. Link here: Assign individuals in set rotation — Smartsheet Community

Cheers,

Ric

KPH

Hi @Hclarke

The formula will depend upon your data. For example, if you set up a sheet that counts the number of visits like this:

(Number of visits is a COUNTIF formula that counts how many times the Team Member / Site pairing occurs in the list)

=COUNTIFS([Team Member]:[Team Member], [Team Member]@row, Site:Site, Site@row) )

Then you could use an INDEX COLLECT formula like this:

=INDEX(COLLECT([Team Member]:[Team Member], Site:Site, [Next Site]@row, [Number of Visits]:[Number of Visits], MIN([Number of Visits]:[Number of Visits])), 1)

To collect the Team member with the lowest number of visits to a given site. And use it to create a table like this:

You could also replace the Number of visits with a Date of visit, if that is more useful.

You do need to make sure each person is listed against each site in the first list. As this takes the minimum value so anyone who does not appear will never be included.

So, I guess my question is, how do you record the past visits?