Data validation/lists

[edsashley]

I have a spreadsheet that contains locations. So have created a drop down list to enable me to select the location and filter using =COUNTIFS(D:D,$N$16,F:F,"<>e-learning*",F:F,"*",H:H,"*")

The D column is the location and N16 contains the drop down box.

The only problem I have is that the location names need to be grouped ie I have North London, East, West, South. The same with other cities. I would like to group these to Just see London, Manchester, Cardiff etc.

How is this done, could someone inform me please.

 

[pbornemeier]

What is the format of the location values?

If it is like this:
Islington, London [N]
Finsbury Park, London [N]
Highbury, London [N]
Holloway, London [N]
Stoke Newington, London [N]
Tottenham., London [N]

Then you could manually add *London*, and *London [N]* to the data validation list to be able to count either a specific suburb, all of London or just the northern suburbs.

 

[edsashley]

Unfortunately they don’t even contain the city name. For example the Cardiff locations are actually ‘Whitchurch’ ‘Cathays’. What I had hoped to do was label a cell as follows:

Cardiff, Whitchurch, Cathays

This way, with the space, Cardiff would appear in my drop down box but the N16 variable would be Cardiff, Whitchurch and Cathays and show records for Whitchurch and Cathays.

 

[pbornemeier]

One way to do what I believe you want would be to add a 'City' helper column that holds the city for each location. You would have to manually update that column to match the location names or use a VLOOKUP against a full list.

If you are using Excel 365 you can use the 'UNIQUE' function to make a list of the unique values in the City column that will automatically adjust as the City column is edited.
If you are using an earlier version then use the Advanced Filter to create a list of unique city values, but it will have to be updated each time the City list is changed.
For the rest of this post the unique data is in Column O. O1 = 'City' and then O2 and below contains the unique values.

Use the Name Manager to create a variable sized area that will adjust to be the size of the unique values.
=OFFSET(Sheet1!O1,1,0,COUNTA(Sheet1!$O:$O)-1,1)

Then assign that named range to the list of the drop down box.

 

[edsashley]

Ok I will try. As long as N16 in the below formula can accept 2 or more different variables I assume it should work.
=COUNTIFS(D:D,$N$16,F:F,"<>e-learning*",F:F,"*",H:H,"*")