In Excel, how to apply filter automatically to a table based on the selection in a drop-down list?

Hi,

Assuming the table is in range A2:F100. Column E has the % entries. In cell H2, you select the % from the drop down. IN cell J2, enter this formula

=filter(A2:F100,E2:E100>H2)

Hope this helps.

Hi,

You will have to use VBA for that. Someone else will help you with the VBA code.

Hi Ji2021! Please try these steps: * Select the cell where you want to create the drop-down list. * Go to the "Data" tab and select "Data Validation". * In the "Data Validation" window, select "List" under the "Allow" option. * In the "Source" field, enter the list of values you want to include in the drop-down list, separated by commas (e.g. 1%, 2%, 3%). * Click "OK" to create the drop-down list in the selected cell. * In the table, add a filter to the column you want to filter based on the selected value. * Click the drop-down arrow in the filter for that column and select "Filter by Condition". * In the "Filter by Condition" window, select "Greater than" and enter the formula "=VALUE(LEFT($A$1,FIND("%",$A$1)-1))/100" (assuming the drop-down list is in cell A1). * Click "OK" to apply the filter. Now, when you select a value from the drop-down list, the table will automatically filter and show only the rows where the value in the selected column is greater than the selected value. Kind Regards, Shakiru

Hi, Shakiru,

I could not find the "Filter by Condition" selection. I am using office 365 and tried "number filters-->custom filter", then select "Greater than" and enter the formula "=VALUE(LEFT($A$1,FIND("%",$A$1)-1))/100" (assuming the drop-down list is in cell A1) but it did not work. No change on table when I make a selection in the drop down list. And it made the data in the table disappear. could not figure out how that happened.

Thanks'

Jim

Hi,

Assuming the table is in range A2:F100. Column E has the % entries. In cell H2, you select the % from the drop down. IN cell J2, enter this formula

=filter(A2:F100,E2:E100>H2)

Hope this helps.

Hi, Ashish,

Your solution works but I have a big table and want to see the filtered result in the same sheet. Is it possible to filter the table directly?

Thanks

Jim

Hi Ji2021!

Please try these steps: * Select the cell where you want to create the drop-down list. * Go to the "Data" tab and select "Data Validation". * In the "Data Validation" window, select "List" under the "Allow" option. * In the "Source" field, enter the list of values you want to include in the drop-down list, separated by commas (e.g. 1%, 2%, 3%). * Click "OK" to create the drop-down list in the selected cell. * In the table, add a filter to the column you want to filter based on the selected value. * Click the drop-down arrow in the filter for that column and select "Filter by Condition". * In the "Filter by Condition" window, select "Greater than" and enter the formula "=VALUE(LEFT($A$1,FIND("%",$A$1)-1))/100" (assuming the drop-down list is in cell A1). * Click "OK" to apply the filter.

Now, when you select a value from the drop-down list, the table will automatically filter and show only the rows where the value in the selected column is greater than the selected value.

Kind Regards, Shakiru