Data Validation drop down has "_" removed

**grcaz** · 09-28-2020, 03:06 PM

Hi - I'm absolutely stumped and have never seen this before. I've taken over another co-workers file and I'm getting familiar with it. They've set up a data validation and I'm not sure how they did it. It's quite impressive.

On a sheet called "Names", they have a list of US States and it includes an "_". IE:

New_Hampshire
New_Mexico
New_York
Ohio
etc, etc.

On another tab, they have the Data Validation in a cell. It refers to the list of states and in the drop down, the "_" is gone. The drop down is:
New Hamphire
New Mexico
New York
Ohio

It look as though there are 2 blank spaces between the word "New" and the 2nd part of the state. I can't seem to find anything unique in looking at the Data Validation.
Allow = List
Source = Names!$C$3:$C$27

There are no macros in this file, so there is no SUBSTITUTION going on (at least that I can see).

Anyone know what the co-worker (who obviously knows Excel better than me!) did?

Thanks!

GRC

**TMS** · 09-28-2020, 03:14 PM

Post a sample workbook. If they are Dependent Dropdown Lists, you can't have space in the name. So, you'd convert it from space to underscore.

**grcaz** · 09-28-2020, 04:17 PM

Thanks for having a look. Hope I uploaded the file correctly!

**TMS** · 09-29-2020, 08:58 AM

I'm really not sure how that is happening. I would normally do it the other way. That is, I'd list the states with a space rather than an underscore and convert it in the Data Validation using

Formula:

Please Login or Register  to view this content.

So, New York would become New_York and you'd have a Named Range for the Cities in New York.

That said, I'd have a matrix with the States across the top and cities listed below. That way, I could have Dynamic Named Ranges for each of the States

**grcaz** · 09-29-2020, 03:34 PM

Yeah, it is really strange (and cool!). I'm now determined to figure out how they did this. Probably going to spend way too much time researching this.

If / When I figure it out, I'll post the solution.

thanks for taking the time to look at though. Much appreciated.

GRC

**Fluff13** · 09-29-2020, 03:51 PM

No real magic involved, if you change the screen resolution you will see the underscores.
Just don't ask me why.

**TMS** · 09-29-2020, 03:52 PM

... if you change the screen resolution you will see the underscores.

Seriously?

**TMS** · 09-29-2020, 03:53 PM

You're welcome.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

**Fluff13** · 09-29-2020, 03:53 PM

Seriously?

Yup, at least that's what happened for me.

**TMS** · 09-29-2020, 04:08 PM

Spent ages looking at it

. Will have another look.

**Fluff13** · 09-30-2020, 12:12 PM

Oops, realised that this is wrong

No real magic involved, if you change the screen resolution you will see the underscores.

No idea why I said screen resolution, it's the zoom setting I was talking about.

**TMS** · 09-30-2020, 04:09 PM

To be fair, I think that's how I interpreted it, though still not had a chance to look.

**grcaz** · 10-02-2020, 11:26 AM

I
feel
stupid....

Thanks for looking at that and resolving it. Kind of relieved it was something so easy. Didn't even realize the zoom setting was @ 80% and not 100%.

Can't believe that in all the years I've been working w/ Excel, I've never seen or heard of that.

Thanks again!

GRC

**Fluff13** · 10-02-2020, 11:31 AM

Must admit I've never encountered it before & it only seems to happen at 80% zoom.

**TMS** · 10-02-2020, 11:56 AM

Don't feel stupid. I spent quite a while looking at it and checking and I could not work out what was going on.

**TMS** · 10-02-2020, 04:40 PM

As mentioned, the attached workbook is how I'd do your Data Validation and Conditional Formatting.

Data Validation drop down has "_" removed

LinkBack

Thread Tools

Rate This Thread

Display

Data Validation drop down has "_" removed

Re: Data Validation drop down has "_" removed

Re: Data Validation drop down has "_" removed

Re: Data Validation drop down has "_" removed

Re: Data Validation drop down has "_" removed

Re: Data Validation drop down has "_" removed

Re: Data Validation drop down has "_" removed

Re: Data Validation drop down has "_" removed

Re: Data Validation drop down has "_" removed

Re: Data Validation drop down has "_" removed

Re: Data Validation drop down has "_" removed

Re: Data Validation drop down has "_" removed

Re: Data Validation drop down has "_" removed

Re: Data Validation drop down has "_" removed

Re: Data Validation drop down has "_" removed

Re: Data Validation drop down has "_" removed

Thread Information

Users Browsing this Thread

Similar Threads

Macro to "Jump to" or find data based on a data validation drop down list

Drop Down Data Validation based on Formula & "Other"

DATA VALIDATION ALLOW TEXT "DROP" and "check"

Help Toggling "Circle Invalid Data" & "Clear Validation Circles" via a single button

[SOLVED] Data validation: allow entry into a cell if other three cells have "X", "Y" and "Z"?

VBA Button "NEXT" next to drop down (data validation) list

Manipulate data presentation using "Drop down list" and "Filter"

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions