Creating a variable validation list

Originally Posted by Kurt Sasse

Help - Creating a variable validation list.

I am trying to create a variable drop down list.

I have a reference Table containing names and multiple ph # for the names.

Example:
A B C D
1 Doe, John (555) 555-1234 (555) 555-2345 (555) 555-1111
2 Smith, Jane (555) 555-9876 (555) 555-8765 (555) 555-2222
3 Jones, Mike (555) 555-4444 (555) 555-6555 (555) 555-3333

Scenario: If you enter a name from A1, you would automatically have a drop down list with the multiple numbers of B1:D1 you could choose from. If you enter a name from A3 you would have a drop down list from B3:D3 to choose from. I would like to put this formula in a validation list.

I have been trying to come up with a variety of nested functions to create this, but am currently unable to. I know its there and I will be obvious when I see it done, but right now I am unable to create it.

Any ideas?

it will be easy for you if use named ranges. (like this)

if A1=Doe, John then give name "Doe_John" to the range B1:D1
if A2=Smith, Jane then give name "Smith_Jane" to the range B2:D2
if A3=Jones, Mike then give name "Jones_Mike" to the range B3:D3
(", " should be replace with "_" while giving names)

and use in Validation List

=INDIRECT(SUBSTITUTE(G1,", ","_"))

(I suppose that you start entering names from G1 downwards. select the range in col G before applying Validation)

hope this would help you.

Regards.

If you want the name in E1 choose data validation, list and choose a1:a3

If the phone number is in E2 choose data validation, list and put the source as
=OFFSET(b1,MATCH(e1,A1:A3,0)-1,0,1,3)

Regards

Dav

A B C D

1 Name Smith, A
2 Phone # (List of Ph#)
3
4 Hm# Cell Other
5 Doe, J (555)555-1234 (555)568-1234 (555)568-9876
6 Smith, A (555)555-2345 (555)568-2345 (555)568-8765
7 Jones, K (555)555-3456 (555)570-3456 (555)570-7654
8 Martin, R (555)555-4567 (555)457-4567 (555)457-6543

What I am trying to do is have a drop down list in B2 depending on what is entered in B1. B1 has a validation list of A5:A8. Example if the name entered in B1 of Smith, A (A6) is chosen from the list A5:A8, Cells B6:D6 is displayed. If you choose another name from the list, the numbers from the right of the name will be displayed to choose from. Does this make sense?

It did work after all, once I figured out and applied it correctly. The last digit was for the amound of clumns I needed. Again Thanks.