Filter in combination with data validation

tvoltagg

Board Regular
Joined
Mar 20, 2006
Messages
159
Hello again geniuses of the excel world, I have another dilemma..

I have 3 columns of data, say, "Name", "Age", "Rank" and have all 3 columns filtered. No problems... BUT, i only want the filter under "Rank" to allow the top 5 as choices.

So assume there are 20 rows of data.
They should be able to pick any of the 20 names in the Name filter, any age in the Age filter, but ONLY the values 1,2,3,4 or 5 in the "Rank" filter.

Is this possible?
Thanks!
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
tvoltagg,

In the 'Rank' filter, click on Custom, and in the left box, pick 'is less than', and in the right box enter the number 6.

Should work.


Have a great day,
Stan
 
Upvote 0
Sorry if I wasn't clear.. I know how to custom filter. What I'm looking for is a way to LIMIT THE CHOICES.
I don't want the user to be able to filter on any rank higher than a certain number.
 
Upvote 0
Hi tvoltagg

You can limit the choice using an auxiliary column.

Example

Your data is in A:C, the headers in row 1

In D1 write Top 5

In D2

= C2<=5

Select A:D, Autofilter, choose TRUE in the autofilter arrow of column D.

Hide column D.

Now the user sees the autofilter in A:C but only gets to choose Ranks 1-5.

Does it solve your problem?
PGC
 
Upvote 0
PGC,

Your solution limits the choices as you are filtering the data yourself.
I need them to be able to SEE all of the rows, but only be able to FILTER by a select group of values (1-5). That is, they should be able to see all 20 rows of data (where the filtered column contains numbers 1-20), but when they click on the filter arrow, they should only see 1, 2, 3, 4 or 5.

Erik,

That would work if the users knew how to use it, which they don't. :)


What I'm asking sounds silly, but it's actually not as I'm just simplifying the data in my question for problem-solving's sake.

Thanks for all of your suggestions! Any more ideas?
 
Upvote 0
In the end, I just created two buttons. One that performs the TopTen rank function, and another to show all.

If anyone knows of something that serves this purpose without the buttons, that'd be great.. but at this point, I think I'm ok.

Thanks for all of your help!
 
Upvote 0
You're welcome
indeed if you don't trust your users being able to use a TOP10filter, then you put some code in the project

next step will be to force the users to enable macros :biggrin:
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top