Hello,
I am definitely beginner level when it comes to excel, so please be patient.
I am trying to create an assignment randomizer at work to remove "favoritism". I want to make this as user friendly as possible.
Different people work on different days so I want to use slicer to pick which teams I want the data to return from. I then want to be able to put a number in a cell to determine how many people to return for that function. I will use a restaurant as an example. Say I need 7 people to grill, 11 people on the fryers, and 3 people at the window and 6 taking orders, I want to be able to put that number in a cell, then only return that amount per area.
From there, I want to hit a button with a macro attached to randomize who does what function, based on the people available from that shift.
Hope that makes sense.
I have the teams portion figured out using slicer, only issue with that is I need it to return only visible data once the team is selected. I have two different formulas to randomize from my roster:
=INDEX(ROSTER!$A$2:$A$60,RANDBETWEEN(1,COUNTA(ROSTER!$A$2:$A$60)),1)
=RANDBETWEEN(1,40) & =RANK.EQ(B3, $B$3:$B$42) + COUNTIF($B$3:B4, B3)
I am then using a VLOOKUP on the dashboard to pull from the 2nd formula.
To randomize I created a macro that just inserts a bunch of "1s" behind a button since the data changes every time there is a change to the worksheet.
Also need help with the amount of people per task.
Thanks
I am definitely beginner level when it comes to excel, so please be patient.
I am trying to create an assignment randomizer at work to remove "favoritism". I want to make this as user friendly as possible.
Different people work on different days so I want to use slicer to pick which teams I want the data to return from. I then want to be able to put a number in a cell to determine how many people to return for that function. I will use a restaurant as an example. Say I need 7 people to grill, 11 people on the fryers, and 3 people at the window and 6 taking orders, I want to be able to put that number in a cell, then only return that amount per area.
From there, I want to hit a button with a macro attached to randomize who does what function, based on the people available from that shift.
Hope that makes sense.
I have the teams portion figured out using slicer, only issue with that is I need it to return only visible data once the team is selected. I have two different formulas to randomize from my roster:
=INDEX(ROSTER!$A$2:$A$60,RANDBETWEEN(1,COUNTA(ROSTER!$A$2:$A$60)),1)
=RANDBETWEEN(1,40) & =RANK.EQ(B3, $B$3:$B$42) + COUNTIF($B$3:B4, B3)
I am then using a VLOOKUP on the dashboard to pull from the 2nd formula.
To randomize I created a macro that just inserts a bunch of "1s" behind a button since the data changes every time there is a change to the worksheet.
Also need help with the amount of people per task.
Thanks