Randomly select a name from a list

O

omni72

New Member
Joined
Nov 8, 2012
Messages
5
I'm looking for a way to randomly select a name from a list, but the catch is that once a name is selected I don't want the populated cell to change (i.e. <gs id="85810d4e-0b81-451c-a115-25a785585cf1" ginger_software_uiphraseguid="26a0d090-069a-43dc-b8a7-bca8421e21ba" class="GINGER_SOFTWARE_mark"><gs id="1bab7459-600f-4948-837d-af07bd40e157" ginger_software_uiphraseguid="b78c16cf-ff11-490d-992c-988487c47eac" class="GINGER_SOFTWARE_mark">the</gs></gs> cell remains persistent when the worksheet is updated). So, something like this:

D2: If C2 is blank, stay blank. If C2 isn't blank, pick one of the names from <gs id="d57f52a3-58d5-4f89-8abe-2c87cadaffdb" ginger_software_uiphraseguid="4b08aa48-2f74-4399-b77d-5f08991179fb" class="GINGER_SOFTWARE_mark"><gs id="40b936f7-7c7d-4f4c-a116-55a0d8164deb" ginger_software_uiphraseguid="4b533c6f-1628-48f8-882d-5878efe6afd9" class="GINGER_SOFTWARE_mark">some</gs></gs> list.

I can accomplish that part of the problem with this formula:

=IF(C2<>"",INDEX(List!$A:$A,RANDBETWEEN(1,COUNTA(List!$A:$A)),1),"")

<gs id="3c6caab3-edf9-43fd-84f4-21c422bf84a1" ginger_software_uiphraseguid="fc23db6d-b36b-4e81-a850-774ed8563404" class="GINGER_SOFTWARE_mark"><gs id="cd396c7e-36f6-446d-a508-1aaefa806d42" ginger_software_uiphraseguid="a6df4296-5d73-4827-8639-dad9e93a21ac" class="GINGER_SOFTWARE_mark">however</gs></gs>, what it won't do is make D2 persistent. The names can repeat (i.e. D3 might be the same as D2), but once a cell is populated it shouldn't change.

I was able to accomplish something similar with the Now<gs id="a4fb2834-66c1-49ac-b562-9d3944c3ea7b" ginger_software_uiphraseguid="bbc5628e-a612-40ec-9766-af53cc5e80c9" class="GINGER_SOFTWARE_mark"><gs id="96943bc2-f2b6-4a19-a67a-de256b06c2d2" ginger_software_uiphraseguid="bfb1b655-a5fa-4625-a7a5-fbd8c4e823c6" class="GINGER_SOFTWARE_mark">(</gs></gs>) function:

=IF<gs id="7a42a81c-6b14-4358-ab49-15f87b3c9552" ginger_software_uiphraseguid="134c8495-290e-47b9-b78b-1095f320d983" class="GINGER_SOFTWARE_mark"><gs id="94a4605a-ab7f-467a-b4dc-a389646d5927" ginger_software_uiphraseguid="416f1eb1-fd5e-42e3-85b5-df33f1a5b573" class="GINGER_SOFTWARE_mark">(</gs></gs>C2<>""<gs id="d742550d-f1d9-4094-b74e-a9610cbc973c" ginger_software_uiphraseguid="134c8495-290e-47b9-b78b-1095f320d983" class="GINGER_SOFTWARE_mark"><gs id="87a27b64-45c5-447c-9655-d44203be4ecc" ginger_software_uiphraseguid="416f1eb1-fd5e-42e3-85b5-df33f1a5b573" class="GINGER_SOFTWARE_mark">,</gs></gs>IF<gs id="48bd7ae0-f96e-41fb-adca-94337398b41b" ginger_software_uiphraseguid="134c8495-290e-47b9-b78b-1095f320d983" class="GINGER_SOFTWARE_mark"><gs id="6fcb8173-016a-40db-b277-2f5b1694acd7" ginger_software_uiphraseguid="416f1eb1-fd5e-42e3-85b5-df33f1a5b573" class="GINGER_SOFTWARE_mark">(</gs></gs>B2=""<gs id="6177aef6-53c3-4f25-b85d-f7a237219ba7" ginger_software_uiphraseguid="134c8495-290e-47b9-b78b-1095f320d983" class="GINGER_SOFTWARE_mark"><gs id="51ea9dfc-3bd9-4f57-b003-e3ca973fb5a8" ginger_software_uiphraseguid="416f1eb1-fd5e-42e3-85b5-df33f1a5b573" class="GINGER_SOFTWARE_mark">,</gs></gs>NOW<gs id="fff7a485-3fe0-48c3-99d3-ceb4a991cbf7" ginger_software_uiphraseguid="134c8495-290e-47b9-b78b-1095f320d983" class="GINGER_SOFTWARE_mark"><gs id="5bddf0f4-0b6e-4f96-9e46-dbca2971bd5f" ginger_software_uiphraseguid="416f1eb1-fd5e-42e3-85b5-df33f1a5b573" class="GINGER_SOFTWARE_mark">(</gs></gs>)<gs id="5b2431e8-db37-4661-af85-ecc92b829485" ginger_software_uiphraseguid="134c8495-290e-47b9-b78b-1095f320d983" class="GINGER_SOFTWARE_mark"><gs id="def0bfc9-040b-4d80-b331-e87bc128bfb6" ginger_software_uiphraseguid="416f1eb1-fd5e-42e3-85b5-df33f1a5b573" class="GINGER_SOFTWARE_mark">,</gs></gs>B2),""<gs id="69ebf92d-6e95-450f-a533-74bdd4d02e66" ginger_software_uiphraseguid="134c8495-290e-47b9-b78b-1095f320d983" class="GINGER_SOFTWARE_mark">)</gs>

Any thoughts or suggestions? And, of course, if this has already been addressed somewhere, I'll be happy to follow the link if someone can provide it. I did search the forum, but didn't come up with anything based on how I was searching for what I'm trying to accomplish.

Thanks, in advance, for your help.

Take care<gs id="ab021e02-dee6-46e1-8e16-64e089dabd22" ginger_software_uiphraseguid="515ed621-eac8-48e6-958f-ea2093a513c5" class="GINGER_SOFTWARE_mark"><gs id="6b9b3c03-afe4-4f36-821a-489e3681a9d1" ginger_software_uiphraseguid="2a3ac046-a62a-4ad1-b454-c0317dbd6001" class="GINGER_SOFTWARE_mark"> -</gs></gs>

omni72
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
It could be done with an event macro. When you enter a value in column C, the macro automatically makes a random selection from the "List" and enters it in column D. The value in column D doesn't change unless you change the value in column C.

Code: 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]Static[/COLOR] RandomizeOnce [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Boolean[/COLOR]
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] RandomizeOnce [COLOR=darkblue]Then[/COLOR] Randomize: RandomizeOnce = [COLOR=darkblue]True[/COLOR]
    [COLOR=darkblue]If[/COLOR] Target.Column = 3 And Target.Count = 1 And Target(1) <> "" [COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]With[/COLOR] Sheets("LIst")
            [COLOR=darkblue]With[/COLOR] .Range("A1", .Range("A" & Rows.Count).End(xlUp))
                Target.Offset(, 1).Value = .Cells(Int(.Count * Rnd + 1)).Value
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]

To instal this code:

  • Right-click on the sheet tab
  • Select View Code from the pop-up context menu
  • Paste the code in the VBA edit window
 
Upvote 0
AlphaFrog, you sir are a prince! :) Thank you very much, I followed your directions & it is performing exactly as I needed it :)

One quick question - is the increased file size a result of the macro? Even with no fields populated, it starts out as a 5.5MB file. If so, no biggie, it was more of a curiosity than anything.

Again, thank you so much for your time & assistance.

Take care -

omni72
 
Upvote 0
omni72 said:
AlphaFrog, you sir are a prince! :) Thank you very much, I followed your directions & it is performing exactly as I needed it :)

One quick question - is the increased file size a result of the macro? Even with no fields populated, it starts out as a 5.5MB file. If so, no biggie, it was more of a curiosity than anything.

Again, thank you so much for your time & assistance.

Take care -

omni72
Click to expand...

You're welcome.

The code should add very little to the file size. I can't speak to the 5.5MB file size without seeing your workbook. Did you format entire rows or columns? Do you have a lot of formulas?
 
Upvote 0
AlphaFrog;4068289<gs id="ea188fb9-e904-4ad2-87bf-8b48864935ae" ginger_software_uiphraseguid="9c58013c-1dd5-48cb-9882-07daf2e969b2" class="GINGER_SOFTWARE_mark"><gs id="c43e963d-65f4-48c2-9e5c-8801325156d6" ginger_software_uiphraseguid="a2e3fb16-b514-4545-bd2e-64dbd9ffccb5" class="GINGER_SOFTWARE_mark"> said:
</gs></gs>You're welcome.

The code should add very little to the file size. I can't speak to the 5.5MB file size without seeing your workbook. Did you format entire rows or columns? Do you have a lot of formulas<gs id="c1014cf8-3d2d-4ea2-9f54-e34a0cfe6fcc" ginger_software_uiphraseguid="18f707b0-7940-4b9b-85d2-01ee7f845df4" class="GINGER_SOFTWARE_mark"><gs id="1848bfdb-82d5-4b77-b1c5-a27f1d47772d" ginger_software_uiphraseguid="0a2ad05c-7b46-4221-986f-64605f48258f" class="GINGER_SOFTWARE_mark">?</gs></gs>
Click to expand...

Sort of figured it out. Okay, that's a lie. But I seem to have it behaving as desired now.

Just for the sake of posterity, here's what I did:

1. Created the original file and added the VBA code
2. Saved it as XLSM (5.5MB)
3. Opened it & the code persisted so I could generate new random entries and re-save (still 5.5MB)
4. Saved as XLSX (16KB)
5. Opened it & the code was gone so I could not generate new random entries
6. Opened a new workbook and literally copied the Sheet1 and List worksheets to the new workbook
7. Pasted the VBA code provided & saved it as an XLSM (22KB)
8. Opened it & the code persisted so I could generate new random entries and re-save (23KB)
9. Smiled at having defeated the mysterious Spacehog Gremlin & wrote this reply :biggrin:

No idea what was going on with the first workbook, but <gs id="e82519fc-8528-4cd7-a3be-8ea0dd28d16f" ginger_software_uiphraseguid="b6fae098-8d1f-4453-b303-751c3fce3863" class="GINGER_SOFTWARE_mark"><gs id="7af1abda-fa91-4466-ba32-20d381b2fd76" ginger_software_uiphraseguid="4debaf17-e0ed-415a-a99e-856cbc10bd42" class="GINGER_SOFTWARE_mark">its</gs></gs> <gs id="22c94864-74f5-44dc-8142-cb2542f770e1" ginger_software_uiphraseguid="b6fae098-8d1f-4453-b303-751c3fce3863" class="GINGER_SOFTWARE_mark"><gs id="7db608fb-8f11-40e2-807c-74afa5d151a9" ginger_software_uiphraseguid="4debaf17-e0ed-415a-a99e-856cbc10bd42" class="GINGER_SOFTWARE_mark">duplicate</gs></gs> seems to be functioning just fine.

Thanks again for your help!

Take care<gs id="da41e409-dd83-4802-aec3-23f179829ef6" ginger_software_uiphraseguid="fb8fc759-c9b8-4af1-aa65-660d1f7942de" class="GINGER_SOFTWARE_mark"><gs id="fa5f3551-8b1d-4076-bdc3-a5e071efaf98" ginger_software_uiphraseguid="e909be8f-795d-4fec-9abf-86dee2aa44e3" class="GINGER_SOFTWARE_mark"> -</gs></gs>

omni72
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Multiple Selections from drop down list
Replies
6
Views
976
MickG
M

Forum statistics

Threads
1,223,238
Messages
6,170,939
Members
452,368
Latest member
jayp2104

Share this page

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
Back
Top