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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Joined
Jul 30, 2006
Messages
3,656
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
 

tvoltagg

Board Regular
Joined
Mar 20, 2006
Messages
159
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.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

Hi,

did you try to filter using TOP10 ?
(you can then adjust to 5 in the dialogbox)

kind regards,
Erik
 

tvoltagg

Board Regular
Joined
Mar 20, 2006
Messages
159
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?
 

tvoltagg

Board Regular
Joined
Mar 20, 2006
Messages
159
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!
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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:
 

Forum statistics

Threads
1,137,335
Messages
5,680,893
Members
419,937
Latest member
Talic

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
Top