Forum Discussion
How to Automate a Rostering System
Hello, and thank you to all who respond constructively.
I am currently attempting at automating a rostering system for 50 employees. They are unionized, and they have the ability to bid on different assignments.
What I am trying to figure out how to do is to have a cell in a column be able to show a value as long as the value is not in the column above it AND to scan the row in which it resides. For example:
| Name | Bid 1 | Bid 2 | Bid 3 | Awarded Bid |
| Gregg | 722 | 711 | 700 | 722 |
| Sam | 722 | 711 | 700 | 711 |
| Dave | 722 | 711 | 700 | 700 |
Though it is quite simple to deal with a small/short example manually in the one above, I am in a position in which all of the employees (50) have more (10) bids. Cross referencing these numbers is more cumbersome.
Now when an employee goes on vacation, I would like to change one value and have the rest of the sheet update. Like the following:
| Name | Bid 1 | Bid 2 | Bid 3 | Awarded Bid |
| Gregg | Vay | Cay | Shun! | N/A |
| Sam | 722 | 711 | 700 | 722 |
| Dave | 722 | 711 | 700 | 711 |
I understand that the easier method would be to write a script with vba, but the company I work for uses Teams for their Excel tools which would stop the script from working.
Thank you, again, for any who have a code or two that would help me out.
- have the rest of the sheet update
Sorry,I am confused about what you meant rest of the sheet? About the automation, as mentioned by NikolinoDE , you may use VBA or Office Scripts, and both tools are connected to another Microsoft solution, which is the Power Automate, which contains several connectors with other Microsoft products and third part ones. As in Excel, the Power Automate community is very active and will be able to help you with the automation share of your solution.
Here are some links about it:
Blog post that compares VBA with Office Scripts: http://digitalmill.net/2023/06/10/office-scripts-the-new-vba/
Power Automate documentation: https://learn.microsoft.com/en-us/power-automate/
Access to Power Automate Community: https://powerusers.microsoft.com/t5/Forums/ct-p/FL_Comm_Forums
=IF(AND(COUNTIF($B$1:B1,B2)=0,COUNTIF(B2:D2,B2)=0),B2,"N/A")
This formula checks if the value in cell B2 is unique in the range B1:B2 and in the range B2:D2. If the value is unique in both ranges, the formula returns the value in cell B2. Otherwise, it returns “N/A”.
You can use this formula to check if an employee’s bid is unique among all bids in the same row and among all awarded bids in the column above. If the bid is unique, it can be awarded to the employee.
However, this formula only checks one bid for one employee. To automate the process of awarding bids for all employees, you may need to use more advanced techniques such as Office Scripts or VBA.
