Help with a data validation formula

**mwood6275** · 01-29-2015, 01:05 PM

I need some help with a formula i cant seem to figure out to save my life.

I have a spreadsheet that we use at our church to track attendees. Starting in column A row 4 i have a named range called AttendeeID. And in column B row 4 i have a named range called AttendeeName.

The AttendeeID's range from d-0001, t-0001, N-0001, etc, depending on how that person has been classified.

What I am looking for is a way to create a validation dropdown list that will look through the AttandeeID's and fill the list with only those names where the AttendeeID = d-whatever. I want to place this dropdown in column C row 4.

The entire sheet is also a named range called AttendeeList if that helps any.

I have used =VLOOKUP("d-????",AttendeeList,30,FALSE) in an empty cell and it will only return the first name in the list and when i placed it in a validation list i get "The list source must be a delimited list, or a reference to a single row or column". I have also tried varios IF statements and i just cant seem to figure this one out.

**Richard Buttrey** · 01-29-2015, 01:10 PM

Hi,

I can do no better than point you in the direction of Debra's Contextures web site where you'll learn everything you ever want to know about dependent drop down lists - and more. See http://www.contextures.com/xlDataVal02.html

**mwood6275** · 01-29-2015, 01:13 PM

i have looked at that and it didn't seem to cover what i am trying to do, unless i am just missing something.

**TMS** · 01-29-2015, 01:23 PM

Please see the attached example.

Regards, TMS

**Richard Buttrey** · 01-29-2015, 01:44 PM

Originally Posted by mwood6275

i have looked at that and it didn't seem to cover what i am trying to do, unless i am just missing something.

...I suspect you are missing something...

**TMS** · 01-29-2015, 02:55 PM

@Richard: not sure it is a dependent list in the way that Debra describes. Unless I also am missing something, there is an initial list and for each entry in that list, there is a dependent list.

In this instance, the OP wants to type a letter in the DV box and get a list relating to that letter. The example I have uploaded allows you to do that. You can type d, t, or n in the box ... anything else will be rejected. Once the letter is in the box, you can either press Enter and click on the arrow, or just click on the arrow. Note that the letter itself must be in the list.

	A	B	C	D	E	F	G	H	I
1	n			d	t	N			Only way I can think of …
2				d	t	n
3				d-0001	t-0001	N-0001			two stage process
4				d-0002	t-0002	N-0002			first, type the letter in cell A1
5				d-0003	t-0003	N-0003			press the down/selection arrow
6				d-0004	t-0004	N-0004			then you can select an ID from the "Group"
7				d-0005	t-0005	N-0005
8				d-0006	t-0006	N-0006
9				d-0007	t-0007	N-0007
10				d-0008	t-0008	N-0008
11				d-0009	t-0009	N-0009
12				d-0010	t-0010	N-0010

Regards, TMS

**mwood6275** · 02-01-2015, 12:18 AM

Thank You so much for all of your help folks.

**TMS** · 02-01-2015, 01:58 AM

You're welcome.

Help with a data validation formula

LinkBack

Thread Tools

Rate This Thread

Display