Random password selector

[jodeyes]

I am trying to create a random password selector with the following parameters.

• I want to put a lot of passwords (around 5-10 columns with 100 passwords in each column) onto a worksheet and hide it
• I want the formula to say "when there is a value in B2, put one of the random passwords from the Password sheet into A1. It doesn't matter if they get used over
• I cannot use a macro because the recipient cannot handle macro-enabled sheets.

Don't even know if this is doable, but hoping someone has an idea how to do this (or a better way!)

Thank you in advance!

 

[footoo]

Here's a possibility.
In your password sheet (named, say, "Hidden") put the passwords in A1:A1000
In B1:B1000 put the formula =RAND()
In the sheet you want to return a random password, enter in A1 :
=IF(B2="","",INDEX(Hidden!$A$1:$A$1000,RANK(Hidden!B1,Hidden!$B$1:$B$1000), 1))

 

[jodeyes]

Thank you, footoo. It worked great, except that whenever I drag the formula down, all of the password values change. Is there any way to accomplish the same thing and keep the passwords static? (I know I can copy and paste as values, but this is being sent to an audience with minimal Excel skills and we are trying to minimize what they will have to do.) Thank you!

 

[footoo]

The problem is that the functions used are volatile and recalculate whenever XL recalculates.
There are non-VBA ways to stop the volatile functions recalculating but they would not be suitable because dragging the formula or copying/pasting wouldn't work.
I do not know a suitable non-VBA way. Perhaps someone else can suggest something.

 

[jodeyes]

Thank you for responding, footoo. I am hoping someone might have another idea. If not, we might have to have our agents copy and paste as values to get static data. Appreciate your help!

 

[footoo]

Here are a few things to consider.

• Instead of having a list of passwords on a hidden sheet, an 8 digit number could be generated by:
=LEFT(TODAY()/ROW()*100000,8)

• If non-numeric characters need to be included, the formula could be written accordingly.
For example :
=REPLACE(LEFT(TODAY()/ROW()*100000,8),3,2,"Ab")
=REPLACE(LEFT(TODAY()/ROW()*100000,8),DAY(TODAY()),2,"Ab")
or tailored to fit requirements and the degree of transparency considered desirable.

• Using ROW() in the formula is a bit transparent, so an alternative is to have a fixed 1000 row table of randomly sorted 2 digit numbers on a hidden worksheet.
The table would consist of sequential numbers from 1 to 1000 in A1:A1000, and randomly sorted numbers from 1 to 99 in B1:B1000.
The above three formulas would then become :
=LEFT(TODAY()/VLOOKUP(ROW(),Hidden!A:B,2)*100000,8)
=REPLACE(LEFT(TODAY()/VLOOKUP(ROW(),Hidden!A:B,2)*100000,8),3,2,"Ab")
=REPLACE(LEFT(TODAY()/VLOOKUP(ROW(),Hidden!A:B,2)*100000,8),DAY(TODAY()),2,"Ab")

• Another way to make them less transparent would be a third column on the Hidden sheet consisting of randomly sorted alpha characters. Then replace the “Ab” in the above formulas with a VLOOKUP.

• The TODAY function is volatile. But the formula result will not change until the next day.
This provides some breathing space to convert the formulas to values.
Conditional formatting could be used to highlight the cells containing formulas as a reminder they need to be converted to values.
The CF formula : =ISFORMULA(A1)

Note : The formulas above assume that only one column will contain the formulas. If more than one column is to be used, the formulas would need revision - otherwise two cells entered in the same row on the same day would return the same password.

 

[footoo]

Correction :

The table would consist of numbers from 1 to 1000 in random sequence in A1:A1000, and randomly sorted numbers from 1 to 99 in B1:B1000.

 

[footoo]

Here's a simpler way :
• In the hidden sheet in A1:A1000 put numbers from 0 to 999 in random sequence, and put your passwords in B1:B1000
• The formula : =VLOOKUP(RIGHT(TODAY()*ROW()*COLUMN(),3)*1,Hidden!$A$1:$B$1000,2)

 

[footoo]

.

 

[footoo]

Another way - with no volatile functions (don't need to convert the formula to value):

• In the hidden sheet in A1:A999 put numbers from 1 to 999, and put your passwords in B1:B999
• On the other sheet, starting in A2 enter : =IF(B2="","",VLOOKUP(RIGHT(MAX(A1,1)/ROW()*1000,3)*1,Hidden!$A$1:$B$999,2))
• A2 returns a password when B2 is not blank.