how to do a drop down menu in excel 2016 youtube

However, since the cell reference changes depending on the division selected by the user, another function should be introduced.

The COUNTA() function comes in handy since it counts cells with numbers or text. This should correspond to the number of apps listed for each division and be dynamic to expand and contract. To fix this, a correction is made by deducting 1 from the MATCH() function. What this means is that when the Productivity division is selected, the MATCH() function returns a “1”, which tells the OFFSET() function to move one column to the right instead of staying in column A. However, this can’t be used as is since the return value of the MATCH() function will be 1, 2, or 3 depending on the selected division.

lookup_array = where the lookup will be done, $A$4:$C$4.

A MATCH() formula comes in handy since it returns a number: In this case, this is dependent on the selected Division by the user.

cols = how many columns to the right until the first value to be used.

In this case, since we want to start one row below the header, we input 1

rows = how many rows going down until the first value to be used.Whenever a formula is to be used as a data validation criteria, it is usually easier to first set the formula up on the spreadsheet, then transfer it to the data validation window so that all the Excel helper tips show up as guides.

How to do a drop down menu in excel 2016 youtube