Data Validation: Named Range as well as an additional value

[jakethepeg1]

I want to set data validation for a cell to a list like this:

• All Cities
• City 1
• City 2
• City 3
• City 4...

Where All Cities is an arbitrary value and the other entries are taken from the headers of a table (=INDIRECT("tbl_Cities[#Headers]")

I need to somehow add the 'all cities' item to the other items in the validation list... I did a search on this and the only answer I found was a flat no. However I'm sure there must be some clever person who could come up with a solution... I need this for a community project I'm working on so any help would be much appreciated. Thanks.

[jakethepeg1]

My primary data manipulation language is SQL... is there an Excel equivalent of UNION?

[FDibbins]

Hi and welcome to the forum

I need to somehow add the 'all cities' item to the other items in the validation list

what exactly do you mean by that?

[jakethepeg1]

Well I need the data validation list to look like the attached image. The city names come from the table headers and the 'All Cities' value is added 'manually'. This help at all?

[jakethepeg1]

Don't know if you know SQL but if I was using that then I'd be doing

Please Login or Register  to view this content.

[jakethepeg1]

And thanks for the welcome...

[newdoverman]

If the table is like this and is called tbl_cities:

All Cities City 1 City 2 City 3 City 4

Go to the Name Manager, New, enter a name like Cities and enter in the formula field =tbl_Cities[#Headers]

Select the cells where you want the Data Validation, go to Data Validation, List, and enter in the formula field =Cities

The drop down list will be populated with the headers of the table.

[jakethepeg1]

Thanks for the response. Unfortunately the 'All Cities' entry isn't in a column in the table...

[jakethepeg1]

See attached for an example of what I want. Thank you.

[newdoverman]

You don't give any indication of what you are really trying to accomplish. There is nothing wrong with the method that you produced the All Cities drop down list. If you want All Cities to be part of the list, add a column to the left of the table and give it the title All Cities. This is done by clicking one of the headings in the table then on the Table Design Tab, click on Resize Table and increase the range in the formula field to include the column on the left for All Cities.

You give no idea of how you are going to use this listing so further information is difficult to give.

[mikeTRON]

What I typically do when I want a dynamic named range that is using something like a pivot table to grab the names is have a blank (as in cleared) drop down equivalent to all. I make a note of it in the dashboard and everyone seems get it quickly. Then instead of referencing the drop down you can reference a hidden cell with an * in it. Or if you are using it for a complex sumifs you can have an If statement to remove that criteria range and criteria from the formula all together.

If this is NOT a dynamic named range then what I would do is create a lists tab and and make the list there manually, once. Link the data validation two THAT list and move on.

[jakethepeg1]

OK. Thanks for everyone's help.
I'm sorry my explanation caused confusion but I don't really see how I can explain it any better... I'll probably go with the extra column on the table (not an ideal solution but probably the easiest).
Thanks again.

[FDibbins]

So, "all cities" is just that, the sum of all the other "named" cities, and not an actual category?

Not sure if you plan on summing or counting, but perhaps we could put an IF () statement together that would sum(?) all the values if "all cities" was sdelected, and individual cities get summed(?) if a city was selected?

[jakethepeg1]

@FDibbins, I don't really know that I understand exactly what you're saying but this is only for a reference field. No calculations are involved. (You are correct about All Cities not being a category)
I simply wanted the user to be able to chose one city from the table or else choose 'all cities' but obviously this isn't possible. Thanks anyway.

[FDibbins]

Thats pretty much what I meant, and yes I know that is a ref cell, but im sure you will have a calc of something based ON that cell?. If you have...
All Cities (no entry is actuaLLy this)
City 1
City 2
etc
if ALL is selected, then it will include all cities in whatever calc is done . See if the attached is what you want?

[jakethepeg1]

Thanks for your effort. That's the result I want however the cities list needs to be dynamic (taken from the header rows of a table which a) can be changed and b) can be added/removed).

[newdoverman]

The enclosed is a small example of Cascading drop down dynamic lists that you may adapt to your situation.

[jakethepeg1]

Thank you for this reply. I have used these before however I hadn't thought of using them in this context so thanks for that suggestion: I'll give it a go.

[newdoverman]

Thanks for the feedback.

Good luck with your project.