Change filter using drop down lists

Attached is a spreadsheet.

There are two drop down menu's in column G and I.

The users of the worksheet have no understanding of filtering what so ever so the aim is to make it as user friendly as possible.

I would like it so that when a value is selected from the drop down this changes the filtered results in the table.

For example when "Angela Elvin" is selected from the drop down in cell G3 then column A will display the rows for "Angela Elvin" only

Likewise when "complete" is selected from the drop down in cell I3 then column E will display the rows for "complete" only

Additional rule

I have added a value to each of the drop down lists which don't appear in either or the corresponding filter columns. The rules for these values have been explained and highlighted in pink on the worksheet.

Good Luck

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
I would move your inputs from the same sheet to the input sheet as when you apply filter that row won't be their and as a result you won't be able to change filters again since that row won't be visible anymore..

So enclosed is the workbook post the above changes..and now when you change your inputs..the filter will change automatically...

Roy CoxGroup Finance ManagerCommented:
Hi Mike

Why not try using Slicers?
Convert the data to a Table and add Slicers.

Take a look at the example and I'll provide fuller instructions if you like it.
mikes6058Author Commented:
Hi Roy,

Unfortunately I can't use slicers as I am using excel 2010 and as I understand slicers can only be used on pivot tables in this version of excel?

Also some of the users of the spread sheet will be using excel 2007 meaning they will have no slicer compatibility at all.
mikes6058Author Commented:
Saurabh Singh Teotia,

The inputs need to be on the same worksheet as the table. They must be clearly labeled and in sight so the user of the work book can easily locate them.

If you are able to move them back could you add an additional value to the second data validation list.

This value will be called incomplete action points...

When selected as an input column E will display all values except "complete"

let me know you thoughts....

Thanks Mike
Saurabh Singh TeotiaCommented:

Here is the logic for not to having in the same sheet like your values is at row-7-->G and I Column..

Now this row has value-->Claire Hallam in A7 which is also the value you want to apply filter on..Now if you choose for instance-->John Evans .. Now that will result in hiding of row-7 because that value is not equal to John Evans...

Now if you want to go back and change your input you need to unfilter the row first to make it visible since it's not visible any more and then only you can select a new value then what you have...

However if you are fine with that..Let me know i will move in the same sheet..Though i won't advise for it because of the above reason as it defeats the entire purpose of automation...


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.