Data validation range?

[Simon4s]

I have a drop down that sorts my data by cities (B1).
All my cells in my sheet ... if = B1, it will sum a row of numbers. Example if B1 = China, all coluns with China in it will get their totals summed together so that my sheet is only showing data from China.

I want to implement a second drop down to show population. My question is, can i put a range in the drop down? such as 5000:5000000. Meaning population between 5000-5,000,000. How do i put this range in the dropdownlist?

Thanks,

 

[BlueHornet]

Your statement is somewhat confusing in several ways, so I would like to clarify before attempting to respond with any suggestion:

1. You mention that you have a "drop down that sorts", and while there are always new things even for this old Excel user to discover about the program, I am not aware of any drop-downs that perform sorting. So I'm wondering if you have some kind of macro called by a button that looks like a drop-down arrow, or are we talking about some kind of AutoFilter (which seems the most likely, given what follows)?

2. You gave the example of "B1 = China", which certainly seems like AutoFilter performance - not sorting - as "all columns with China in it will get their totals ... so that my sheet is only showing data from China". That's certainly a description of a filter, not a sort.

3. Have you tried applying the same kind of AutoFilter to your population column and filtering as you suggest?

If we're talking about some kind of macro, which sorts and AutoFilters the data in your worksheet, then the response could get complicated. But if you're talking about simply adding another (manual) AutoFilter to your "population" column and then using it to filter a range of cities ... just try it. You won't hurt anything.

My basic confusion is still with the titling of the issue as "Data validation range", since you're not really discussing data validation at all, but filtering.

 

[Simon4s]

Sorry. I made a dropdown list in "B1" with population "1000-5000", "5001-10000"

If below it (c1)i wrote, =if(Z1=B1, yes, no)
And lets say Z1=6000. Z1 is between 5001-10000
how do i make the list so that "=if(Z1=B1, yes, no) " will work?

Right now, unless Z1=5001-10000, C1 will always say no

 

[BlueHornet]

the picture is getting somewhat clearer - maybe.

When you say that you "made a drop down list in B1", it seems that you have attempted to create a Data Validation list (hence the title of your issue).

However, you seem to have some confusion on how to use it (and how to make it in the first place, but we'll get to that later).

"Data Validation" is a method to validate data ENTRY. You'd use a data validation list, for example, if you wanted to make a list of "All Countries in Asia", for example, so that you could then enter new rows of "City" information and pick the country name from the drop-down (data validation) so that all of the country names were properly spelled, for example.

You're talking about using your data validation values as filter criteria, however. While I won't say "that's impossible" - because people can make Excel do amazing contortions sometimes - it is a misuse of the process. There are easier ways to do what you want.

What you want is an AutoFilter.

In your data table, which we'll assume has columns of data for such things as "Country", "City", "Population" and others, go to the row that has those column names, select the whole row, and set up an AutoFilter at Data / Filter.

Using the "drop-down-style arrows" next to each of the column labels, you can select "Country" to be "China", for example, and then go to the "Population" drop-down-style arrow, click it, and see an ordered list of all populations for the listed cities in China, from lowest to highest. You can use the check boxes to select the populations you want to view. (You can also do the same selection in other ways, but ... baby steps for now.)

Remember: Data Validation / drop-downs are for validation of new data entry. That's their purpose. Don't get hung up on trying to use that process to filter data.

Finally, you wouldn't want to have specific data validation on a number value like Population, except that you COULD set up data validation to make sure that entries are "at least" so high and "at most" so high. For example, you could set up City Population d.v. list that would ensure that population is listed in "whole numbers", and is "between low value and high value" and other criteria. You would not set up a data validation entry in a list such as you propose: 5000-10,000; it wouldn't work.

 

[Simon4s]

Am i able to generate reports and summaries with filters? I already have everything setup in excel so that when i select a region, reports will be made for just that region. There are ~10 sheets and yes I am doing what you suggested in the last paragraph.