Is there anyways to use a drop down list (created using data validation tool) to automatically apply a filter to your table when a selection is made in the drop down.

Example: say you have a column that is filled with months and the header is “month”, is there away to use a drop down table located at the top corner of the file to automatically apply a filter for just that month when a selection is made?

I do know I can just use the regular filter on the table, however, this is a massive file and there will be many people using it. There will be a vlookup linking to the drop down menu that will pull in their specific budgets when selected, it would just be a luxury to have it filter for their rows as well when selected.

Do users need to read the data, or to edit/input data?

The hairs on the back of my neck are standing up hearing "large file" "lots of users" "vlookups/tables/filters" - I'd suggest looking at a simple PowerBI dashboard if users only need read access to the data. Its extremely end user friendly, and you can customize lots of button style filters that manipulate table or matrix data.

This can be done with VBA.

Two key parts to it:

1) Making a VBA function operate on cell value change found here: https://docs.microsoft.com/en-us/office/troubleshoot/excel/run-macro-cells-change
2) Updating tables with VBA, here's a reference that might help: https://www.get-digital-help.com/quickly-filter-a-column-in-an-excel-table/

Have fun, but also probably just do what cascadian recommended hahahah

Also you are a war criminal if you use vlookup instead of index match - you're creating static references that break with columns and also hogging resources and slowing your sheet. Probably save it as .xlsb if it is a large file (i've built models that are 300 MB in xlsx but down to 100 MB in xlsb)

This post was edited by Omega310 on Oct 12 2021 10:55pm