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!

 

[jodeyes]

Wow, footoo, this is a lot to digest! I'm still testing out your formulas to figure out what would work best in our scenario. Thank you so much for continuing to think about the problem and helping me solve it. I will respond again once I've finished testing.

 

[jodeyes]

Ok, trying the last item you suggested, but running into an issue I cannot figure out. I think I need to reset a bit. Here's what my data looks like:

I have a 2-digit location code in Cell B3

My columns are as follows:
Cell A7 = User ID
Cell B7 = Password
Cell C7 = Employee ID (entered by the user in a variety of formats)
My first row of data is 8.
Col A = User ID - currently this is a concatenate formula using the 2-digit site code entered by the user in cell B3, plus the employee ID submitted. (I got this formula from another Mr. Excel user.) It is:

=B$3&TEXT(RIGHT(SUBSTITUTE(MID(C11,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C11&"0123456799")),255,"-",""),6),"000000")

So, I want the password to "appear" in cell B when there is an 8-digit number in cell A. (Because I'm using a concatenate formula, the 2-digit location code shows up, even if there isn't any data in the Employee ID field.)

Sample:
A7 User ID B7 Password
A8 Formula above - 32123456 B8 Need formula to calculate password if cell A8 has an 8-digit code.

For testing purposes, I have a sheet called Hidden with numbers 1-99 in Col A and random passwords in Col B.

I hope this is a little clearer. In reading my original post, I think I either got the cell references mixed up, or I had the spreadsheet laid you differently than I do now.

Thanks again for your help. I just cannot get your last formula to work with your cell references and can't seem to figure out how to translate.
Jodi

 

[footoo]

Try this :
• On the Hidden sheet, enter numbers from 0 to 99 in column A and the passwords in column B
• On the data sheet, insert a new column after column A
• in C8 enter
=IF(AND(LEN(A8)=8,LEN(B8)=3,ISNUMBER(B8)),VLOOKUP(RIGHT(ROUND((B8/ROW()*1000),0),2)*1,Hidden!$A$1:$B$100,2),"")
• The user must enter any random 3-digit number in B8 and then, if A8 contains 8 characters, the password will show in C8.

 

[jodeyes]

Tried it, but couldn't get it to work. I cannot add a new column B or ask my users to put in random 3-digit numbers. I did test it on my existing column C which is the Employee ID. My first test sample is just 123456, so I changed your formula to be "=6" instead of "=3," but I got an #N/A result. (Unfortunately, that Employee ID will not consistently be 6 whole numbers, but a combination of different numbers of digits and sometimes include hyphens and letters, so I'm not sure this is even the way to go, but I wanted to see if I could get your formula to work.)

Here's the formula I used:
=IF(AND(LEN(A8)=8,LEN(C8)=6,ISNUMBER(C8)),VLOOKUP(RIGHT(ROUND((C8/ROW()*1000),0),2)*1,Hidden!$A$1:$B$99,2),"")

My boss is now saying that maybe we should just go with just do an IF/VLOOKUP and living with the fact that the passwords will be somewhat static. So, this would be the logic:
If A8 is 8 numbers, go to the Hidden tab and select the password located in cell B1,
If A9 is 8 numbers, go to the Hidden tab and select the password located in cell B2, etc.

I am still hoping to find something a little more elegant and closer to what I want, but I will settle, if needed. I have to demo this new template next Monday, so have to do whatever is needed to finalize it by then.

Thank you for your help!
Jodi

 

[footoo]

It works for me.

The ID numbers could only be used in the formula if they have some common attributes that can be extracted by the formula.

If you want a "static" way : =IF(A8="","",VLOOKUP(ROW(),Hidden!$A$1:$B$999,2))

 

[footoo]

but I got an #N/A result

Make sure the numbers in column A of Hidden sheet are from 0​ to 99.

 

[jodeyes]

Duh, thanks. That was the problem. Since I do have a field that will always be 2-digits, I modified the references to that "new column" you told me to add to that cell reference and it worked! This is fantastic! Thanks so much for sticking with me through this, Footoo!! I will show my boss later today and we are well ahead of schedule. Can't thank you enough.

 

[jodeyes]

Ok, we tested it out and we kept having repeating passwords. Tried adding a lot more passwords to see if that would help, but it didn't. We couldn't figure out the logic behind the formula to tweak it. Yeah, we're noobs! So, I went back to the drawing board and followed your original instructions. Added a new Col B and put in random 3-digit numbers and now the password returns a blank. So, I guess we're not done after all. Any ideas on how to make the password selection more random? Seems like the math is rounding to the same number, then picking the same password for more than one person. We don't need it to be 100% unique, but would like the selection to be enough variety so that you would not see a repeating password within the same sheet of 10-50 users, which is our typical request.

 

[footoo]

You must be setting something up wrongly.
What formula and layout are you using?

 

[jodeyes]

Hi Footoo - thank you for hanging in there with me. It's really difficult to use this forum without being able to post attachments or screenshots. This all could have gone much faster and smoother if we could have done that. (I tried one of the screenshot tools that was mentioned on this forum, but couldn't get it to work in a timely manner.)

So, my boss took your formula and added a piece and now it works fine. Here's what we ended up with:

=IF(AND(LEN(A13)=8,LEN($B$3)),VLOOKUP(RIGHT(ROUND((($B$3/ROW()*1000))+ROW(),0),2)*1,Hidden!$A$1:$B$100,2),"")

Using this formula, we didn't get any repeating passwords on the list.

A13 is the UserID and B3 is the 2-digit location code that every agent has to type in on the form. For this example, we only have 100 passwords on the list, but we'll expand that to 1000 or more to add more variety.

I still have to test out the formula above, but wanted to send an update while I had a few minutes. Probably won't get back to this until tomorrow.

Thank you again for helping us get this far!