Excel Create Dynamic List from Table (3 Easy Ways)

In general, lists are made by extracting entries from a dataset. If the lists update automatically then the lists are dynamic lists. In this article, we describe some of the easiest ways to create a dynamic list from a table. We use the FILTER function and combination of INDEX, OFFSET, COUNTA, and COUNTIF functions as well as the Data Validation feature to create dynamic lists from tables.

Suppose, we have a table and we want a dynamic list of products under any or no conditions.

Dataset-Excel Create Dynamic List from Table

 

Dataset for Download

3 Easy Ways Excel Create Dynamic List from Table

Method 1: Using Filter Function (Under a Condition)

From the dataset, we want a dynamic list of specific Products that have a Total Sale equal to or more than $100. We combine FILTER, OFFSET, and COUNTA functions to achieve the purpose.

Make sure you have the Excel version of Office 365 to use the FILTER function. Otherwise, you won’t be able to execute this method. Versions of Office apart from Office 365 don’t support the FILTER function

Step 1: Paste the following formula in any blank cell (i.e. G3).

=FILTER(OFFSET($B$3,0,0,COUNTA(B:B)-1,1),OFFSET($E$3,0,0,COUNTA(E:E)-1,1)>=100)

Here, in the formula,

COUNTA(B:B); pass the number of rows in column B then COUNTA(B:B)-1,1; returns the number of total rows subtracting the header row number.

OFFSET($B$3,0,0,COUNTA(B:B)-1,1); pass all the product’s name in the table. The combination of OFFSET and COUNTA functions keeps the formula dynamic.

OFFSET($E$3,0,0,COUNTA(E:E)-1,1)>=100; returns affirmative for all the products that have Total Sale equal or more than $100.

In the end, FILTER(OFFSET($B$3,0,0,COUNTA(B:B)-1,1),OFFSET($E$3,0,0,COUNTA(E:E)-1,1)>=100; returns all the product’s name that have a Total Sale equal or more than $100.

Filter function-Excel Create Dynamic List from Table

Step 2: Press ENTER. Then you will see all the Products’ Names that have a Total Sales equal to or more than $100 will appear.

Filter function result-Excel Create Dynamic List from Table

Read More: How to Create Dynamic List in Excel Based on Criteria (Single and Multiple Criteria)

Method 2: Using INDEX OFFSET COUNTA COUNTIF and MATCH Function (Under a Condition)

If you don’t have an Office 365 subscription, you can create a dynamic list by combining multiple functions like INDEX, OFFSET, COUNTA, COUNTIF, and MATCH.

Step 1: Insert the below formula in any blank cell (i.e. G3).

=INDEX(OFFSET($B$3,0,0,COUNTA(B:B)-1,1),MATCH(SMALL(IF(OFFSET($E$3,0,0,COUNTA(E:E)-1,1)>=50,OFFSET($E$3,0,0,COUNTA(E:E)-1,1),””),ROW(A1:INDIRECT(“A”&COUNTIF(E:E,”>=50″)))),OFFSET($E$3,0,0,COUNTA(E:E)-1,1),0),1)

Inside the formula,

OFFSET($B$3,0,0,COUNTA(B:B)-1,1); returns Products depending on row numbers excluding the column title,

MATCH(SMALL(IF(OFFSET($E$3,0,0,COUNTA(E:E)-1,1)>=50; matches products depending on the condition of equal or more than $50.

ROW(A1:INDIRECT(“A”&COUNTIF(E:E,”>=50″)))); displays the rows that affirm the condition.

Combined functions-Excel Create Dynamic List from Table

Step 2: Hit CTRL+SHIFT+ENTER altogether as it’s an array function. Then the resultant value appears.

Combined function result

Step 3: Drag the Fill Handle and the rest of the Products that fulfill the condition appears.

Combined function final result-Excel Create Dynamic List from Table

Read More: How to Create a Dynamic Top 10 List in Excel (8 Methods)

Method 3: Using Data Validation Feature 

In order to create a dynamic drop-down list from a table, we can use the UNIQUE function and Data Validation. The UNIQUE function is necessary to achieve a Spill Range inside the Data Validation Course option.

The UNIQUE function only works in Office 365. It’s unavailable in other versions of Office.

Step 1: Add a Helper Column of Products using the formula below to accommodate the Spill Range option.

=UNIQUE($B$3:$B$16)

Data validation header column-Excel Create Dynamic List from Table

Step 2: Press ENTER. All the entries in Column Product appear.

Data validation helper column

Step 3: Select any blank cell (G3). Go to Data Tab > Data Validation (in Data Tools section). Data Validation window will appear.

Data validation feature

Step 4: In the Data Validation window, Choose Settings> List (in Allow drop-down menu)> H3, put a Hashtag sign(#) after it to make it a Spill Range.

Data validation options

Step 5: Click OK. A drop-down list box will appear in cell G3. And all the products can be seen there as a dynamic list from the table.

Data validation final result-Excel Create Dynamic List from Table

Read More: How to Make a Dynamic Data Validation List Using VBA in Excel

Conclusion

In this article, we extract a dynamic list from a table. In doing so, we use functions such as FILTER, INDEX, OFFSET, COUNTA, COUNTIF, and MATCH as well as excel features like Data Validation. The FILTER function and parts of the Data Validation feature are only available for Office 365 subscribers but you can use Method 2 to overcome this. Hope you find discussed methods worthy of your search. Comment, if you need further clarifications or have something to add.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo