random selection from a specific worksheet to show in a specific cell in another worksheet

[Blanchetdb]

Hi

As the title states, I am seeking to have a command button on sheet "Home Page". When selected, a random selection would happen on sheet "Audit-Staffing" in column F. The result would populate in cell B2 on sheet "Monitoring".

another twist is that in column AO of sheet "Audit-Staffing" in each row there is a value of True or False. True value would mean that this file has already been audited so that selection should no longer be an option.

Can someone provide me with assistance?

much appreciated;

thank you
Dan

 

[kevin9999]

Rather than using VBA, you could use a formula to get the random name you seek. Try the following to see if it gives you what you want. Instead of a command button/macro, simply press the F9 key when on the Monitoring sheet to recalculate. I've assumed that column AO of the Audit-Staffing sheet is a genuine TRUE or FALSE value & not a text string "TRUE' or "FALSE". Adjust the bottom row in the formula (currently 29) to whatever you need for your list of names.

RANDOM AUDIT.xlsx
	F																																		AN	AO
1	Staff member																																			T/F
2	Adam																																			TRUE
3	Brenda																																			TRUE
4	Charles																																			FALSE
5	Doris																																			TRUE
6	Eric																																			TRUE
7	Frida																																			TRUE
8	George																																			TRUE
9	Harriet																																			TRUE
10	Ivan																																			FALSE
11	Joan																																			TRUE
12	Ken																																			TRUE
13	Louise																																			TRUE
14	Mark																																			FALSE
15	Nora																																			FALSE
16	Oliver																																			FALSE
17	Peta																																			FALSE
18	Quinten																																			FALSE
19	Ruth																																			FALSE
20	Stan																																			TRUE
21	Theresa																																			TRUE
22	Usman																																			TRUE
23	Vera																																			FALSE
24	Will																																			FALSE
25	Xena																																			TRUE
26	Yorich																																			TRUE
27	Zara																																			TRUE
Audit-Staffing

RANDOM AUDIT.xlsx
	A	B
1
2	Here:	Quinten
Monitoring

Cell Formulas
Range	Formula
B2	B2	=INDEX('Audit-Staffing'!F2:F29,LARGE(IF('Audit-Staffing'!AO2:AO29=FALSE,ROW('Audit-Staffing'!AO2:AO29)-ROW('Audit-Staffing'!AO2)+1),INT(RAND()*COUNTIF('Audit-Staffing'!AO2:AO29,FALSE)+1)))

 

[Blanchetdb]

Rather than using VBA, you could use a formula to get the random name you seek. Try the following to see if it gives you what you want. Instead of a command button/macro, simply press the F9 key when on the Monitoring sheet to recalculate. I've assumed that column AO of the Audit-Staffing sheet is a genuine TRUE or FALSE value & not a text string "TRUE' or "FALSE". Adjust the bottom row in the formula (currently 29) to whatever you need for your list of names.

RANDOM AUDIT.xlsx
	F																																		AN	AO
1	Staff member																																			T/F
2	Adam																																			TRUE
3	Brenda																																			TRUE
4	Charles																																			FALSE
5	Doris																																			TRUE
6	Eric																																			TRUE
7	Frida																																			TRUE
8	George																																			TRUE
9	Harriet																																			TRUE
10	Ivan																																			FALSE
11	Joan																																			TRUE
12	Ken																																			TRUE
13	Louise																																			TRUE
14	Mark																																			FALSE
15	Nora																																			FALSE
16	Oliver																																			FALSE
17	Peta																																			FALSE
18	Quinten																																			FALSE
19	Ruth																																			FALSE
20	Stan																																			TRUE
21	Theresa																																			TRUE
22	Usman																																			TRUE
23	Vera																																			FALSE
24	Will																																			FALSE
25	Xena																																			TRUE
26	Yorich																																			TRUE
27	Zara																																			TRUE
Audit-Staffing

RANDOM AUDIT.xlsx
	A	B
1
2	Here:	Quinten
Monitoring

Cell Formulas
Range	Formula
B2	B2	=INDEX('Audit-Staffing'!F2:F29,LARGE(IF('Audit-Staffing'!AO2:AO29=FALSE,ROW('Audit-Staffing'!AO2:AO29)-ROW('Audit-Staffing'!AO2)+1),INT(RAND()*COUNTIF('Audit-Staffing'!AO2:AO29,FALSE)+1)))

Hi

So the formula did produce a result as soon as I entered the formula in B2 but not sure how the F9 key comes into play because when I press it, nothing happens; it does not recalculate. Also will this always produce the next match in the list? so if the next row has a False in AO, then it will select that one or does it really randomly select a row that has False in column AO. There is no real end to column AO as new entries will continue to be added to the Audit-Staffing sheet

 

[kevin9999]

Not sure why the F9 key does not recalculate your sheet for you. When I press it, using the dummy data described in post#2 I get this:
First press:

RANDOM AUDIT.xlsx
	A	B
2	Here:	Mark
Monitoring

Cell Formulas
Range	Formula
B2	B2	=INDEX('Audit-Staffing'!F2:F27,LARGE(IF('Audit-Staffing'!AO2:AO27=FALSE,ROW('Audit-Staffing'!AO2:AO27)-ROW('Audit-Staffing'!AO2)+1),INT(RAND()*COUNTIF('Audit-Staffing'!AO2:AO27,FALSE)+1)))

Second press:

RANDOM AUDIT.xlsx
	A	B
2	Here:	Vera
Monitoring

Cell Formulas
Range	Formula
B2	B2	=INDEX('Audit-Staffing'!F2:F27,LARGE(IF('Audit-Staffing'!AO2:AO27=FALSE,ROW('Audit-Staffing'!AO2:AO27)-ROW('Audit-Staffing'!AO2)+1),INT(RAND()*COUNTIF('Audit-Staffing'!AO2:AO27,FALSE)+1)))

Third press:

RANDOM AUDIT.xlsx
	A	B
2	Here:	Peta
Monitoring

Cell Formulas
Range	Formula
B2	B2	=INDEX('Audit-Staffing'!F2:F27,LARGE(IF('Audit-Staffing'!AO2:AO27=FALSE,ROW('Audit-Staffing'!AO2:AO27)-ROW('Audit-Staffing'!AO2)+1),INT(RAND()*COUNTIF('Audit-Staffing'!AO2:AO27,FALSE)+1)))

So you see that the next selection is not 'in order' but randomly selected from the list. I'm no sure if the solution proposed will work for you though, as the formula only seems to work correctly if the range is exactly matching your data range. A VBA solution (or a better formula) may be the answer. Are you able to provide a sample of your actual data (you can disguise the names - replace them all with ="Name "&Row() ) using the XL2BB add in, or alternatively, share your file via Google Drive, Dropbox or similar file sharing platform?

 

[Blanchetdb]

Not sure why the F9 key does not recalculate your sheet for you. When I press it, using the dummy data described in post#2 I get this:
First press:

RANDOM AUDIT.xlsx
	A	B
2	Here:	Mark
Monitoring

Cell Formulas
Range	Formula
B2	B2	=INDEX('Audit-Staffing'!F2:F27,LARGE(IF('Audit-Staffing'!AO2:AO27=FALSE,ROW('Audit-Staffing'!AO2:AO27)-ROW('Audit-Staffing'!AO2)+1),INT(RAND()*COUNTIF('Audit-Staffing'!AO2:AO27,FALSE)+1)))

Second press:

RANDOM AUDIT.xlsx
	A	B
2	Here:	Vera
Monitoring

Cell Formulas
Range	Formula
B2	B2	=INDEX('Audit-Staffing'!F2:F27,LARGE(IF('Audit-Staffing'!AO2:AO27=FALSE,ROW('Audit-Staffing'!AO2:AO27)-ROW('Audit-Staffing'!AO2)+1),INT(RAND()*COUNTIF('Audit-Staffing'!AO2:AO27,FALSE)+1)))

Third press:

RANDOM AUDIT.xlsx
	A	B
2	Here:	Peta
Monitoring

Cell Formulas
Range	Formula
B2	B2	=INDEX('Audit-Staffing'!F2:F27,LARGE(IF('Audit-Staffing'!AO2:AO27=FALSE,ROW('Audit-Staffing'!AO2:AO27)-ROW('Audit-Staffing'!AO2)+1),INT(RAND()*COUNTIF('Audit-Staffing'!AO2:AO27,FALSE)+1)))

So you see that the next selection is not 'in order' but randomly selected from the list. I'm no sure if the solution proposed will work for you though, as the formula only seems to work correctly if the range is exactly matching your data range. A VBA solution (or a better formula) may be the answer. Are you able to provide a sample of your actual data (you can disguise the names - replace them all with ="Name "&Row() ) using the XL2BB add in, or alternatively, share your file via Google Drive, Dropbox or similar file sharing platform?

I actually get a #NUM! error.

I don't expect the data to go passed row 2000 so the range could be from F2:F2000 but I can't have a return of a blank cell.

I can ensure that the value in Rows 2 to 2000 is False as a default, but will that possibly return a blank option? Also, the F9 function does not work for me neither.

What I would really like to be able to is have a command button next to each Branch

that would activate the random selection for the corresponding Branch sheet (i.e Audit-Staffing) and then one last button at the button that would activate the Monitoring sheet.

on Monitoring sheet, there would be a cell next to each Branch with a populated random selection.

I have been doing some searching and it is possible using VBA but unfortunately a lot of the examples I come across are too complex for me to understand in order for me to adapt it to my situation.

 

[kevin9999]

Again, could you provide a sample using the XL2BB add in, or share your file via Google Drive, Dropbox or similar file sharing platform? You can't copy from an image.

 

[Blanchetdb]

Again, could you provide a sample using the XL2BB add in, or share your file via Google Drive, Dropbox or similar file sharing platform? You can't copy from an image.

Google Drive: Sign-in

Access Google Drive with a Google account (for personal use) or Google Workspace account (for business use).

drive.google.com

password: Staffing

 

[kevin9999]

You have to make the shared file available to anyone with the link...

 

[Blanchetdb]

You have to make the shared file available to anyone with the link...

I do use this method often ... sorry

its done

 

[kevin9999]

Thank you, I have it now, and by the looks of it I will be passing on this one. I note your workbook comprises (among other things) around 50+ forms - not my area at all I'm afraid. Hopefully, another volunteer on the forum will be able to rise to the challenge. Good luck & best wishes