Data Validation Drop Down Question?

[davinroach]

I have a weird Question. I have Created Multiple Pivot Tables that are linked together with a data validation drop down. I've used INDIRECT to connect them via the drop down tables in my excel sheet. However, I have created the tables and they have SPACES in places where I WANT THEM. This helps better quickly navigate the drop down menu so that I am quickly find what I am looking for. With that being said some of the Spaces that I want do not show up there. Sometimes they do but often times they do not. Does any one have any suggestions. I think I have Here is the Data Validation Not Showing The Space.

Here is a screen shot of my table that clearly has spaces in it.

 

[awoohaw]

I'm not sure if it happens wiht everyone with 365, but this does with me. It takes some explaining.
At one time I had to create unique ranges to make the list for drop down to reference.

But, more than a year ago, i noticed that when I referenced a range that had repeated items for a drop down that it took the duplicates out.
Since you have multiple "blank" cells in your list, you may be getting a filtered list like I do. It is a feature I like.

By the way? Are you on the public version of 365 or a Insider "Current Version"?

Oh, welcome to the forum.

As you see, only one William and one John are in the list, although the validation range includes two of each.

It also seems to deduplicate blanks:

 

[davinroach]

I'm not sure if it happens wiht everyone with 365, but this does with me. It takes some explaining.
At one time I had to create unique ranges to make the list for drop down to reference.

But, more than a year ago, i noticed that when I referenced a range that had repeated items for a drop down that it took the duplicates out.
Since you have multiple "blank" cells in your list, you may be getting a filtered list like I do. It is a feature I like.

By the way? Are you on the public version of 365 or a Insider "Current Version"?

Oh, welcome to the forum.


View attachment 111132

View attachment 111131

As you see, only one William and one John are in the list, although the validation range includes two of each.

It also seems to deduplicate blanks:
View attachment 111133

Thank you for getting back to me. I believe it's a public version but its the software on my computer. Do you know how to change it to where it will allow duplicates? But yes thank you for having me. This has already been very helpful. Thank you!

 

[awoohaw]

Thank you for getting back to me. I believe it's a public version but its the software on my computer. Do you know how to change it to where it will allow duplicates? But yes thank you for having me. This has already been very helpful. Thank you!

No idea how to change it

You could use multiple spaces in the blanks you want. the first one has no space: "", the second, one space: " ", the third two spaces: " ", etc.
(But what happens when your user picks one of those choices? Are there errors or alerts elsewhere in your sheet?

 

[davinroach]

No idea how to change it

You could use multiple spaces in the blanks you want. the first one has no space: "", the second, one space: " ", the third two spaces: " ", etc.
(But what happens when your user picks one of those choices? Are there errors or alerts elsewhere in your sheet?

No there aren't errors. I thought about maybe just numbering them. Trying that. Because there are so many tables that I am using so IDK. But I will give it my Best go! Thank you.

 

[awoohaw]

well. A quick, though not clean way to make a bunch of seemingly blank cells would be to highlight the range of the entire list press F5, click special, find blanks.
Then without exiting any cells, type
=rept(" ",row())
and then press
CNTL-ENTER

 

[davinroach]

well. A quick, though not clean way to make a bunch of seemingly blank cells would be to highlight the range of the entire list press F5, click special, find blanks.
Then without exiting any cells, type
=rept(" ",row())
and then press
CNTL-ENTER

Awesome! Thank you very much I'll try it now