Assigning a drop down value to a filter

Copper Contributor

Hello All, 

I am new to the group so please just point to where this question would be best put to. 

I have a table that can be filtered by selecting the drop downs.  However, What would be better is to have the table filter itself if I could automatically assign a value to the drop down from elsewhere. It could point to a another cell and then that would update my filtered table.  Is there a way to do this?  

 

Mig

2 Replies

@mig14 A very similar question was asked just yesterday, and the suggestions made can be viewed here: Custom Number Filtering 

 

If the solutions presented there are not what you're looking for, or if you're unsure about how to implement them in your workbook, please share some additional details, such as screenshots of sample data, so someone might be able to assist you further.

 

Kind regards.

@mig14 

You can automatically assign a value to a drop-down list, which in turn will update your filtered table. This can be achieved using Excel's data validation and formulas.

Here is a step-by-step guide:

Assuming you have a drop-down list in cell A1 and a table to be filtered in columns B and C:

1. Create a List for the Drop-Down:

You can create a list of values in a separate range (e.g., in column D) or use a named range. Let us assume you have the list in cells D1:D3.

2. Create the Drop-Down List:

Select cell A1.

Go to the "Data" tab.

Click on "Data Validation."

In the Data Validation dialog box:

      1. Choose "List" in the Allow dropdown.
      2. In the Source field, enter =D1:D3 (or your range).

3. Set Up Filtered Table:

Assuming your table headers are in row 5 and the data starts in row 6, you can use the following formula to filter your data:

In cell B6 (and copy it down for other cells in column B):

=IF(OR($A$1="", $A$1=B5), C6, "")

This formula checks if the value in A1 is empty or matches the department in column B. If true, it returns the corresponding value in column C; otherwise, it returns an empty string.

4. Adjust Your Table Headers:

Make sure to adjust your table headers (if needed) to reflect that they are now formula-driven and might have some empty cells.

Now, when you select a value from the drop-down in cell A1, the corresponding rows in column B will display the values based on your filter condition.

This approach uses a formula to conditionally display data based on the selected drop-down value. Adjust the ranges and cell references according to your actual data structure.

The text and steps were edited with the help of AI.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.