The named range is called "nut" and the value in B6 is "Nut" but the INDIRECT function correctly resolves to the named range, even though case differs. Note: the approach we are taking here is not case-sensitive. Test the dropdown lists to make sure they dynamically respond to values in column B. In this formula, INDIRECT simply evaluates values in column B as references, which links them to the named ranges previously defined.ĥ. Create a data validation rule for the dependent dropdown list with a custom formula based on the INDIRECT function: =INDIRECT(B5) Also note data validation with a list works fine with both horizontal and vertical ranges – both will be presented as a vertical dropdown menu.Ĥ. Note: just to be clear, the named range "category" is used for readability and convenience only – using a named range here is not required. Create and test a data validation rule to provide a dropdown list for Category using the following custom formula: =category 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. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |