If statement and Data Validation - NEED spaces

[kristak]

Hello all,

I've been an unregistered guest for quite some time on this forum, this is the first time I've been unable to find a workaround for my needs in the answered questions, so here is my first registered post! I found this post (http://www.excelforum.com/excel-gene...tion-list.html) to be similar to what I'm looking for, but not exact.

I am working on a form that looks like "1form.jpg" (attached) on sheet2.
1form.JPG

It pulls data from "1reference.jpg" on sheet1.
1reference.JPG

I am looking to populate a list of lakes in each county, based on what county is chosen. The reason I have been unable to use data validation is that one of my counties has two words: Green Lake. At this point I am unwilling to put an underscore in or remove the space, simply to keep the ability to publish the file to PDF for distribution later on. (The end users of that PDF want a polished, clean look.)

Is there a formula I've overlooked that can accomplish this? I appreciate any and all help; this is one of many roadblocks I'm encountering in a massive database I'm building.

[Richard Buttrey]

Hi and welcome to the front row!

Have you considered using Dependent Drop Down list functionality? Check out Debra's site here http://www.contextures.com/xlDataVal02.html

where you'll find all you need to know on this subject.

[protonLeah]

You should be using named ranges for all lists, vis., counties, lake names, etc.
You can use validation formulas such as the below to remove spaces in one list that reference another list:

Please Login or Register  to view this content.

[kristak]

Thanks to both of you! I must have been too focused on how it wasn't working to find the spot in the post where it was explained. I had that link to Debra's "Contextures" site open, it was one of my first references when looking this up. Works like a charm.

[Richard Buttrey]

Glad to have helped and thanks for the rep. As you've probably found Debra's site is a mine of information about Excel stuff.