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 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).
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.
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.
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).
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.
Step 2: Hit CTRL+SHIFT+ENTER altogether as it’s an array function. Then the resultant value appears.
Step 3: Drag the Fill Handle and the rest of the Products that fulfill the condition appears.
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.
Step 2: Press ENTER. All the entries in Column Product appear.
Step 3: Select any blank cell (G3). Go to Data Tab > Data Validation (in Data Tools section). Data Validation window will appear.
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.
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.
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.