200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Hi Team,
Can someone help me create a lucky draw but with exemption. I have 3 rows:
1) Numbers 1-150
2) Names of 150 Students
3) Comment section Exempted and Not Exempted
I am currently using =(RANDBETWEEN(1,150), however the exempted students can still be drawn if I click F9. Is there a way we can exclude the numbers/names with comment "Exempted" during draw?
can we use RANDBETWEEN with multiple range? if not, what formula fits best for my situation? thank you
You have your sheet set up with 3 rows and 150 columns, correct?
If you switch to 3 columns with 151 rows (first row as a header row) your problem is easy to solve.
I have attached a workbook which shows this (add formatting to suit your tastes). It involves some simple steps handled by macros:
1) Data is entered on sheet1 ("Roster") ~ I added data validation in the "Comment" field to prevent misspelling.
2) Sheet2 ("Sorted Roster") requires no data entry - it refeences "Roster" to get all of the information.
3) On the "Roster" sheet the "UPDATE" 'button" (just a simple shape) has the macro "Up_Date" assigned to it. Clicking this does all the work.
4) After updating "Roster" data as needed, click the "UPDATE" button. This will: i) sort the data on the "Sorted Roster" sheet so all "Not Exempted rows are listed first; ii) pick a random row number for the rows with "Not Exempted"; iii) display the name from column "B" of that row in cell "F2" of the "Roster" sheet.
Hope you find this helpful.
Doug
