Random selection of cell information from a group of cells

**Icecycle66** · 07-22-2009, 09:33 AM

I am working with Excel 2007.

In my spread sheet I have:

l A l 001 l
------l 002 l
------l 003 l

This means "A" is equal to "001", "002", or "003". It does not matter which.

I want that whenever I type "A" into a cell, that "A" is changed to either "001", "002", or "003". I want it to change randomly between the three options so that"A" is not always "002", or whichever.

I want the formula to say: IF "A", then randomly change to "001", "002", or "003".

**oldchippy** · 07-22-2009, 09:38 AM

Hi,

You could try this in B1

=IF(A1="","",IF(A1="A",RANDBETWEEN(1,3),""))

Then custom format B1 to 000

**Icecycle66** · 07-22-2009, 11:37 AM

Alright, I've figured this much out.

=IF(S1="A",INDEX(R1:R2,RANDBETWEEN(1,COUNTA(R1,R2)),1))

Now, if "A" is found in cell S1 the formula will randomly pick between posting the information from cells R1 and R2. If there is no "A" the formula returns "FALSE". Good.

The next problem is how do I tack on other formulas in that cell. The letter "A" is covered. Now I want that same cell to look to find "B", "C", etc and, if found choose randomly from their respective pools of possible representative numbers.

So that, if I type "A" in S1 I get either 001 or 002.
If I type "B" in S1 I get 003, 004, or 005.
If I type "C" in S1 I get 006, 007, 008, or 009.
etc
etc

How do I make that happen?

**oldchippy** · 07-22-2009, 01:25 PM

Does each additional letter randomly select another additional digit?

For example D could be 010 to 014?

**Icecycle66** · 07-22-2009, 01:40 PM

Yes.
I have attached an example of what should happen, I hope this helps you help me.

However, the numbers that go along with the letters are not really sequential as shown in my example.
The true numbers are more like:

A=1919191
1919201
1233212

B=0000000011
0000001100
0000000110

an so on with random sequences.

**oldchippy** · 07-22-2009, 05:44 PM

Hi,

Does this help? I've used this formula in E1 and custom formatted the cell to 000

=RANDBETWEEN(VLOOKUP(D1,$A$1:$B$21,2,FALSE)+0,VLOOKUP(D1,$A$1:$B$21,2,FALSE)+0+2)

**Icecycle66** · 07-23-2009, 09:46 AM

The formula works properly, as it is written.

However, I don't think I have expressed properly what I need.
Attached is a copy of the worksheet.
The possible numbers of a letter which they represent are set. (Columns "A" and "B"). In column "D" I type one letter and in column "E" one of the choices of letter representation is choosen and displayed at random.

The Formula you have provided works well, but it changes the number available to represent the letter by adding "1" or "2"; somehow.

Also, I can't seem to get the format of "00000000 000" to display, all I get is #value!. Even after changing custom and standard formats.

**oldchippy** · 07-23-2009, 10:42 AM

Hi,

Well I must admit I'm stumped

I can see you posted your original question in the Programming Forum and now that I've seen your spreadsheet that's the best place for it. When I first saw your original question I throught I could do it with formulas.

I suggest that you post again in programming, but under a new title and attaching your last spreadsheet, but you could also provide a link to this thread as well so that they can see not to do anything like I did.

Sorry I can't help, only VBA is not my expertise - good luck