Return random visible data from slicer

thaught06

New Member
Joined
Nov 24, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • Randomizer Help 2.PNG
    Randomizer Help 2.PNG
    22.9 KB · Views: 5

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Watch MrExcel Video

Forum statistics

Threads
1,123,279
Messages
5,600,696
Members
414,400
Latest member
Damocles2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top