fishoutofwater
New Member
- Joined
- Jul 20, 2011
- Messages
- 38
In A1 through Ax in each cell I have one of five names. For example: Fred, Sally, Bob, Sue and Jared. Cells B1 through Bx I have some data and I have named this B Range "saltest".
In D1 I am trying to generate a single item from the B range (saltest.) So far so good. After getting some help here (thanks everyone!) I worked this out:
INDEX(saltest,ROUND(RAND()*COUNTA(saltest),0))
But here is the catch, In C1 I have someone's name from the A1 through AAx list. Say 3 lines have "Fred" I need my RAND answer to only come from the Fred choices... for example...
A ------ B -------- C ------------- D
Fred -- Hello ----- Fred ----- **Pick a random choice from any Fred B's**
Jared -- Uggg
Sally -- Frog
Fred -- Howdy
Fred -- Hi There
Jared -- Sigh
Sally -- Penguin
Sally -- Cat
Jared -- Drat
So I'd want each f9 re-rand to give me something from the Fred Set only: Hello, Howdy, Hi There. Then, if I change C1 to Sally, I'd want the RAND to come from the Sally options: Frog, Penguin, Cat.
In D1 I am trying to generate a single item from the B range (saltest.) So far so good. After getting some help here (thanks everyone!) I worked this out:
INDEX(saltest,ROUND(RAND()*COUNTA(saltest),0))
But here is the catch, In C1 I have someone's name from the A1 through AAx list. Say 3 lines have "Fred" I need my RAND answer to only come from the Fred choices... for example...
A ------ B -------- C ------------- D
Fred -- Hello ----- Fred ----- **Pick a random choice from any Fred B's**
Jared -- Uggg
Sally -- Frog
Fred -- Howdy
Fred -- Hi There
Jared -- Sigh
Sally -- Penguin
Sally -- Cat
Jared -- Drat
So I'd want each f9 re-rand to give me something from the Fred Set only: Hello, Howdy, Hi There. Then, if I change C1 to Sally, I'd want the RAND to come from the Sally options: Frog, Penguin, Cat.