Drop Down Menu: How to attach a behavior so that only certain specific rows are shown?

Hi all,

I'm using Excel 2010 and I have a worksheet where I need to create a drop down menu in a cell. This drop down menu will have a list like: Stand, Transport, F&B Expenses.

I need to attach a behavior to the drop down menu so that when the user chooses, say, F&B Expenses, then the table just below that drop down menu display only the rows where F&B expenses (located in a specific column) are shown.

Can someone please point me in the right direction?

Ok Fotis, here is a sample file.

In fact, I need the drop down menu to be in cell F2. The list that will be in the drop down will be the items mentioned in the Column F (F18 to F100). If you look into that range, you will notice that we have several cells where, for example, Transport is mentioned. So when the user clicks transport in the Drop Down Menu, I want only rows where "Transport" exist to appear. It would be real nice if some magic could happen to give only the sum for "Transport" at G101 and same thing for Columns J to Column V!!

I guess I will need an "ALL" item also in the drop down list so that all the rows can again be displayed if the user want it.

Thanks for your help!


sample 4.xlsx

Thank you Fotis. Sorry for the not so "small" file!

You got my goal "partly"...in fact, when the user chooses on of the items in the list, I want the table below to only display the items chosen. For example, if user selects "Sales Tools", the table should display only rows 10,15,16 and 17.

of all the columns!

May I am not clear...let me try again: if the user selects "Accommodation" in the drop drown list, rows having items other than "Accommodation" in Column F disappears!!
You get the idea?

Ah ok! Thanks for your help!

Doesn't the Sort button (filter options) on the table's column header provide similar functionality to what you are wanting?

jhren, it does in a sense but I wanted it to pair with the drop down list along with the sums being updated with respect to the item being selected in the drop down list.

Originally Posted by zicitron

jhren, it does in a sense but I wanted it to pair with the drop down list along with the sums being updated with respect to the item being selected in the drop down list.

Your Table was acting strange on my system, so I copied to new sheet, and had to make it into a table again. Total row across bottom updates on filtering now. (Accidententally muffed the table formatting in the transfer... sorry.)

Can't help with pairing the drop down list with filtering.

sample4 .xlsx