how do you fix the drop down menu in excel 2016

Rest assured, there is a method to the madness :) Why complicate a nice simple formula with INDIRECT? Similarly, INDIRECT will convert the text 'A1:A10' into the range A1:A10 inside another function: =SUM(INDIRECT('A1:A10')Īt first glance, you might find this construction annoying, or even pointless. For example, INDIRECT will take the text 'A1' and turn it into an actual reference: =INDIRECT('A1') INDIRECT accepts text values and tries to evaluate them as cell references. The key to this technique is named ranges + the INDIRECT function. Where the worksheet contains the following named ranges: The data validation in column B uses this custom formula: =categoryĪnd the data validation in column C uses this custom formula: =INDIRECT(B5) If the user selects 'Fruit', they see a list of fruits, if they select 'Nut', they see a list of nuts, and if they select 'Vegetable', they see a list of vegetables.

In the example shown below, column B provides a dropdown menu for food Category, and column C provides options in the chosen category. Read on to see how to create dependent dropdown lists in Excel. This may sound complicated, but it is actually very simple, and a great example of how INDIRECT can be used. They are created with data validation, using a custom formula based on the INDIRECT function and named ranges. These kind of lists are called dependent dropdowns, since the list depends on another value. a list of foods that depends on category.a list of models that depends on manufacturer.a list of flavors that depends on type of ice cream.a list of cities that depends on the selected country.

But once you start to use dropdown menus in your spreadsheets, you'll inevitably run into a challenge: how can you make the values in one dropdown list depend on the values in another? In other words, how can you make a dropdown list dynamic? In column E, the choices are Complete, Pending, or Cancelled, and these values are pulled automatically from the range G5:G7:ĭropdown lists are easy to create and use. Dropdown lists are implemented as a special kind of data validation. This makes it easy for users to enter only data that meets requirements. Dropdown lists allow users to select a value from a predefined list.

How do you fix the drop down menu in excel 2016