How to create multiple dependent drop-downs using array formulas in Excel?

Drop-down lists are a very important part of any dashboard or data entry form. In this article, we’ll learn how to create multiple dependent drop-downs using array formulas. What I mean by multiple dependent drop-downs is: When selecting an item from the first drop-down, the following drop-downs would show only relevant items based on your selection.

Creating dependent drop-down lists in excel could be easy if done using VBA, but it becomes trickier to do it with excel formulas alone. For this, we can use ‘Array formulas’. An array is a collection of one or more items. Likewise, an array formula can return one or more items as result. Array formulas must be entered in a cell by pressing Ctrl + Shift + Enter. In the formula bar, you can see any array formula wrapped inside curly braces {}. For example, {SUM(A1:A5)}

We have sample data as follows:

Multiple drop downs sample data

Using the above data, we want to create dependent drop-downs like shown below. To create these drop-down lists, I will be using two sheets: “Sample Data” & “Temp”.

Excel drop down example

Creating a list with unique values for first drop-down

In Column A of ‘Sample Data’ , you can see there are repeated values. We need to eliminate them before we can show it in our first drop-down . There are many ways to make a list unique. One option that is often used is ‘Remove duplicates’ feature from ‘Data’ tab of Excel’s menu ribbon. But using that is a manual task and must be repeated, when new data would be added. Hence, it is not preferred. Another option to get the unique list would be to use array formulas. The below formula can be used to get the unique values from column A of ‘Sample Data’.

Write the below formula in the “A2” cell of “Temp” sheet and remember to press Ctrl + Shift + Enter! Drag the formula till “A11” cell of “Temp” sheet.

IFERROR( INDEX( SampleData!$A$3:$A$11,MATCH( 0, COUNTIF( $A$1:A1,SampleData!$A$3:$A$11), 0) ) ,””)

  • COUNTIF function would return the number of occurrences of the in the sample data range
  • MATCH function will look-up for”0” in the output of COUNTIF and returns the row number which has the next unique brand name
  • INDEX function picks up & shows the text of the next unique brand name from the respective row number given by MATCH
  • If MATCH does find anymore unique names on the sample data range, then it’ll return #N/A error instead of row number, which would be handled by IFERROR function

Thus, unique list of values for column A of Sample Data would be obtained in “Temp” sheet.

First dynamic range drop-down

To populate the obtained unique values into a drop-down list, we’d use the data validation feature from ‘Data’ tab of Excel’s menu ribbon. Since we don’t know how many unique brand names could be there at any point in time, we cannot use a fixed range to populate the drop-down. We must use a dynamic range to populate the drop-down list.

In cell “A16” of “Sample Data” sheet, write the below formula in the Data Validation window and click OK as shown in the below Figure to get the dynamic drop-down list as shown below.

OFFSET(Temp!$A$2,,,COUNTIF(Temp!$A$2:$A$30,”?*”),)

  • COUNTIF function would return the number of rows with brand names
  • OFFSET function would select the number of rows as given by COUNTIF from A2 and populate the dynamic drop-down list

Dynamic range selection using data validation

 

Creating single drop down list

Second dependent dynamic range drop-down

Creating the second drop-down involves the same task as we did before. But, the second drop-down should populated on selecting an item from the first drop-down list. Thus, it should check the selected item in the first drop-down (currently in “A16” cell of “Sample Data” sheet) an get only the relevant items. We achieve that by using the “IF” function.

Compared to the previous formula, the change in this array formula is the input array for the INDEX function. In previous formulas, the input was fixed to SampleData!$A$3:$A$11 . Here, the input array in which the unique values must be found should be based on the selection made on the previous drop-down and hence, is dynamically obtained using OFFSET formula.

Write the below formula in cell “B2” of “Temp” sheet, press Ctrl+Shift+Enter & drag the formula till “B11”.

IFERROR(

INDEX(OFFSET(SampleData!$B$2,MATCH(SampleData!$A$16,SampleData!$A$3:$A$11,0),0,COUNTIF(SampleData!$A$3:$A$11,SampleData!$A$16),1),

IF(AND( ISERROR( MATCH( 0, COUNTIF( $B$1:B1, OFFSET( SampleData!$B$2, MATCH( SampleData!$A$16, SampleData!$A$3:$A$11, 0), 0, COUNTIF( SampleData!$A$3:$A$11, SampleData!$A$16), 1) ), 0) )=TRUE, $B1=”HeaderName”), 1,MATCH(0,COUNTIF($B$1:B1, OFFSET( SampleData!$B$2, MATCH( SampleData!$A$16, SampleData!$A$3:$A$11, 0), 0, COUNTIF( SampleData!$A$3:$A$11, SampleData!$A$16), 1) ), 0) )),””)

In Cell “B16” of “Sample Data” sheet, using data validation, enter the below formula and click OK to get the dependent dynamic second drop-down list. Eventually, the output would look as shown below.

OFFSET(Temp!$B$2,,,COUNTIF(Temp!$B$2:$B$30,”?*”),)

Creating dependent drop-down using data validation and array formulas

Multiple dependent dynamic range drop-downs

Any number of further dependent drop-downs we create would follow the same procedure, but for more than two drop-down selections, the above formulas would become very long & difficult to handle. Instead of IF condition, we can use logical AND concept to get the same output. So, we can use the below formula from third drop-down to all further ones.

Write the below formula in cell “C2” of “Temp” sheet to get the unique values for third drop-down.

IFERROR(INDEX(SampleData!$C$3:$C$11,AGGREGATE(15,3,((SampleData!$A$16=SampleData!$A$3:$A$11)* (SampleData!$B$16=SampleData!$B$3:$B$11))/((SampleData!$A$16=SampleData!$A$3:$A$11)* (SampleData!$B$16=SampleData!$B$3:$B$11))*(ROW(SampleData!$B$3:$B$11) – ROW(SampleData!$B$2)), ROWS($B$1:$B1))),””)

  • AGGREGATE function is used here to get the smallest number in the range. The * operator performs logic AND operation.

In Cell “C16” of “Sample Data” sheet, using data validation, enter the below formula and click OK to get the dependent dynamic third drop-down list.

OFFSET(Temp!$C$2,,,COUNTIF(Temp!$C$2:$C$30,”?*”),)

The final output after we repeat the process for fourth and fifth drop-downs, would look as shown in the below Figure.

Multiple dependent drop down list using array formulas

Conclusion

Creating drop-down with array formulas is fun. I hope this article would help you in creating more sophisticated dashboards with multiple dependent dynamic drop-downs.

 

Check out our Tableau Pages

How to get a "Print PDF" Button in Excel?

<div>One of the features in Excel is the ability to convert Excel files to PDF format. In this article we explain how to create a button in the excel worksheet, which when clicked generates a PDF file of that sheet.</div>
<div>Let us take the picture below as example to take a PDF and save it in current folder.</div>
<div style=”text-align: center;”><img class=”aligncenter size-full wp-image-1187″ title=”Selected file for PDF” src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/Selected-file-for-PDF.png” alt=”” width=”550″ height=”230″ /></div>
<h3>Below are the steps to get a button, which converts the excel file to a PDF file:</h3>
<h5><img class=”alignnone size-full wp-image-1188″ title=”1″ src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/1.png” alt=”” width=”46″ height=”45″ align=”middle” /> <span style=”font-size: 14px; font-weight: normal;”>Select </span><strong style=”font-size: 14px;”>Developer</strong><span style=”font-size: 14px; font-weight: normal;”>, select </span><strong style=”font-size: 14px;”>Insert</strong><span style=”font-size: 14px; font-weight: normal;”> from </span><strong style=”font-size: 14px;”>Control</strong><span style=”font-size: 14px; font-weight: normal;”> group and select </span><strong style=”font-size: 14px;”>Button</strong><span style=”font-size: 14px; font-weight: normal;”> from </span><strong style=”font-size: 14px;”>Form Controls</strong><span style=”font-size: 14px; font-weight: normal;”>.</span></h5>
<div><img class=”aligncenter size-full wp-image-1189″ title=”Insert Button” src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/Insert-Button.png” alt=””

width=”331″ height=”184″ /></div>
<h5><img class=”alignnone size-full wp-image-1190″ title=”2″ src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/2.png” alt=”” width=”46″ height=”45″ align=”middle” /> <span style=”font-size: 15px; font-weight: normal;”>Drag the control on the spreadsheet, a window appears.</span></h5>
<h5><img class=”alignnone size-full wp-image-1191″ title=”3″ src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/3.png” alt=”” width=”46″ height=”45″ align=”middle” /> <span style=”font-size: 14px; font-weight: normal;”>Enter the name of the macro in the tab provided for </span><strong style=”font-size: 14px;”>Macro name</strong><span style=”font-size: 14px; font-weight: normal;”> and click </span><strong style=”font-size: 14px;”>OK</strong><span style=”font-size: 14px; font-weight: normal;”>.</span></h5>
<div><img class=”aligncenter size-full wp-image-1199″ title=”Assign Macro” src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/Assign-Macro.png” alt=”” width=”381″ height=”366″ /></div>
<h5><img class=”alignnone size-full wp-image-1192″ title=”4″ src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/4.png” alt=”” width=”46″ height=”45″ align=”middle” /> <span style=”font-size: 14px; font-weight: normal;”>To edit the name of the button, Right click on the button and select </span><strong style=”font-size: 14px;”>Edit Text</strong><span style=”font-size: 14px; font-weight: normal;”>.</span></h5>
<div><span style=”font-size: 14px; font-weight: normal;”><img class=”aligncenter size-full wp-image-1200″ title=”Edit Text” src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/Edit-Text.png” alt=”” width=”293″ height=”233″ /> </span></div>
<h5><img class=”alignnone size-full wp-image-1193″ title=”5″ src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/5.png” alt=”” width=”46″ height=”45″ align=”middle” /> <span style=”font-size: 14px; font-weight: normal;”>Right click on the button and select </span><strong style=”font-size: 14px;”>Assign Macro</strong><span style=”font-size: 14px; font-weight: normal;”>.</span></h5>
<div><span style=”font-size: 14px; font-weight: normal;”><img class=”aligncenter size-full wp-image-1201″ title=”Assign Macro” src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/Assign-Macro1.png” alt=”” width=”381″ height=”366″ />
</span></div>
<h5><img class=”alignnone size-full wp-image-1194″ title=”6″ src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/6.png” alt=”” width=”46″ height=”45″ align=”middle” /> <span style=”font-size: 14px; font-weight: normal;”>Select the </span><strong style=”font-size: 14px;”>Macro name</strong><span style=”font-size: 14px; font-weight: normal;”> and select </span><strong style=”font-size: 14px;”>New</strong><span style=”font-size: 14px; font-weight: normal;”>.</span></h5>
<div><span style=”font-size: 14px; font-weight: normal;”><img class=”aligncenter size-full wp-image-1202″ title=”Assign New Macro” src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/Assign-New-Macro.png” alt=”” width=”391″ height=”373″ />
</span></div>
<h5><img class=”alignnone size-full wp-image-1195″ title=”7″ src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/7.png” alt=”” width=”46″ height=”45″ align=”middle” /> <span style=”font-size: 14px; font-weight: normal;”>A Microsoft Visual Basic window appears.</span></h5>
<div style=”text-align: center;”><span style=”font-size: 14px; font-weight: normal;”><img class=”aligncenter size-full wp-image-1209″ title=”Visual Basic Application” src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/Visual-Basic-Application2.png” alt=”” width=”539″ height=”262″ />
</span></div>
<h5><img class=”alignnone size-full wp-image-1196″ title=”8″ src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/8.png” alt=”” width=”46″ height=”45″ align=”middle” /> <span style=”font-size: 14px; font-weight: normal;”>Enter the below code in the application where the arrow is pointed, under ‘</span><strong style=”font-size: 14px;”>Sub CreatePDF()’.</strong></h5>
<blockquote>
<div>Sub CreatePDF()</div>
<div>ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _</div>
<div>”Generate Report.pdf”, Quality:=xlQualityStandard, _</div>
<div>IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= False</div>
<div>End Sub</div></blockquote>
<div style=”text-align: center;”><img class=”aligncenter size-full wp-image-1205″ title=”Code to Generate PDF” src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/Code-to-Generate-PDF.png”

alt=”” width=”600″ height=”200″ /></div>
<h5><img class=”alignnone size-full wp-image-1197″ style=”font-size: 11px; font-weight: bold;” title=”9″ src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/9.png” alt=”” width=”46″ height=”45″ align=”middle” /> <span style=”font-size: 14px; font-weight: normal;”>Close the Microsoft Visual Basic application, now just click on the </span><strong style=”font-size: 14px;”>CreatePDF</strong><span style=”font-size: 14px; font-weight: normal;”> button. That’s all a PDF will be generated and saved in the same folder.</span></h5>
<h5><img class=”alignnone size-full wp-image-1206″ title=”10″ src=”http://www.p2w2.com/blog/wp-content/uploads/2011/11/101.png” alt=”” width=”50″ height=”45″ align=”middle” /> <span style=”font-size: 14px; font-weight: normal;”>If we want to change the name of the PDF, we can change it in the code. For example in the second line we have “Generate Report.pdf”, change it to “</span><strong style=”font-size: 14px;”>Create Report.pdf</strong><span style=”font-size: 14px; font-weight: normal;”>”.</span></h5>
<div></div>
<div><strong>————————————————————————————————————–</strong></div>
<strong>
<div>Vani is a Business Associate with p2w2, a Spreadsheet Solutions company. p2w2 has expertise in <a href=”../../chaitanya_sagar/”>Excel Modeling</a>, <a href=”../../excel-dashboard-reporting.php”>Excel Dashboards</a>, <a href=”../../cost-profitability-analysis.php”>Profitability Analysis</a>,  <a href=”../../custom-invoices.php”>Excel Invoices</a>and <a href=”../../custom-invoices.php”>Excel Bid sheets</a>. You can contact us by email: cs [at] p2w2.com or call us at 305.600.0950.</div>
</strong>
<div></div>

free copy of text your ex backhow to win back your ex girlfriend quickly How To Make Your Ex Girlfriend Want You Back Quickly we just spoke after breaking up what nexthow to make your ex want you