In other words, you must make sure that the named ranges you created match the values in the Category dropdown list.ģ. Important: the column headings in E4, F4, and G4 must match the last three named ranges above ("vegetable", "nut", and "fruit"). In the example, create a list of fruits, nuts, and vegetables in a worksheet.Ģ. Create named ranges for each list: category = E4:G4, vegetable = F5:F10, nut = G5:G9, and fruit = E5:E11. This section describes how to set up the dependent dropdown lists shown in the example.ġ. So, in this example, we're picking up the text values in column B, and using INDIRECT to convert them to cell references by matching existing named ranges, like this: =INDIRECT(B6)ī6 resolves to the text "nut" which resolves to the range G5:G9. INDIRECT maps text to a named range, which is then resolved to a valid reference. In the example on this page, we're combining the latter idea with named ranges to build dependent dropdown lists. ![]() You can pick up text values on a worksheet, and use them like a cell reference in a formula.You can assemble a text reference inside a formula, which is handy for certain kinds of dynamic references.The beauty of INDIRECT is that it lets you use text exactly like a cell reference. 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. Right-click any cell in your source range, and select Insert from the dropdown menu.Dropdown lists allow users to select a value from a predefined list. Excel will dynamically update your range selection in the data validation settings to include the new range that you’ve increased by one cell.ġ. Instead, you’ll need to insert a new entry somewhere in the middle of the range. You won’t be able to add an item to the end of the range, because your range selection is limited by the first and last cells you’ve selected. ![]() In addition to changing items in your range to update your lists, you can also add new items. You can have the contents of a single range control the items for all of those lists, and it doesn’t matter how many there are. This method is best when you want to format a lot of cells into a dropdown using the same list items. Any change you make there will update every dropdown list you created where you selected this range as the source. The nice thing about this approach is that you can change any of the items in that list just by modifying any cell in the range.
0 Comments
Leave a Reply. |