See also: One of the most useful features of data validation is the ability to create a dropdown list that let users select a value from a predefined list. Dropdown lists make it easy for users to enter only data that meets your requirements. Dropdown lists are easy to create and use. But once you start to use dropdown menus to 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? Here are some examples:.
A drop down list is actually a tiny window from where you can peek into some other data you have constructed. This also gives you the possibility of controlling exactly what can be entered into a cell. This makes the drop-down menu a very common and powerful data validation tool. Numbers, as I suppose Excel can, could also lookup in a separate table, so for simplicity's sake, you could store you data in one table and do your 'work' in another. If the table is particularly large, however, you'll see a performance degradation in Numbers (at least I do on my 1.66 GHz 1G Mac mini.
a list of cities that depends on the selected country. a list of flavors that depends on type of ice cream. a list of models that depends on manufacturer. a list of foods that depends on category These kind of lists are called dependent dropdowns, since the list depends on another value. They are created with data validation, using a custom formula based on the. This may sound complicated, but it is actually very simple, and a great example of how INDIRECT can be used. Read on to see how to create dependent dropdown lists in Excel.
Dependent dropdown example In the example shown below, column B provides a dropdown menu for food Category, and column C provides options in the chosen category. 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. The data validation in column B uses this custom formula. = ( ( 'A1:A10' ) = (A1:A10 ) At first glance, you might find this construction annoying, or even pointless. Why complicate a nice simple formula with INDIRECT? Rest assured, there is method to the madness:) The beauty of INDIRECT is that it lets you use text exactly like a cell reference.
This provides two key benefits:. You can assemble a text reference inside a formula, which is handy for. You can pick up text values on a worksheet, and use them like a cell reference in a formula. In the example on this page, we're combining the latter idea with named ranges to build dependent dropdown lists. INDIRECT maps text to a named range, which is then resolved to a valid reference. 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. = (B5 ) = ( 'nut' ) =G5:G9 B5 resolves to the text 'nut' which resolves to the range G5:G9.
How to set up dependent dropdown lists This section describes how to set up the dependent dropdown lists shown in the example. Create the lists you need.
In the example, create a list of fruits, nuts, and vegetables in a worksheet. 2. Create named ranges for each list: category = E4:G4, vegetable = F5:F10, nut = G5:G9, and fruit = E5:E11. Important: the values in E4, F4, and G4 must match the last three named ranges above (vegetable, nut, and fruit). In other words, you must make sure that the named ranges you created are named to match the values that will appear in the Category dropdown list. = (B5 ) In this formula, INDIRECT simply evaluates values in column B as references, which links them to the named ranges previously defined. Test the dropdown lists to make sure they dynamically respond to values in column B.
Note: the approach we are taking here is not case-sensitive. 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. Dealing with spaces Named ranges don't allow spaces, so the usual convention is to use underscore characters instead. So, for example, if you want to create a named range for ice cream, you would use icecream. This works fine, but dependent dropdown lists will break if they try to map 'ice cream' to 'icecream'.
To fix this problem you can use a more robust custom formula for data validation.
Sorry in advance if I make any English mistakes since it's not my first language. Now, to my problem.
Is it just me or it doesn't seem to be possible to create a 'dropdown menu' with my chapters on OneNote 2016 for Mac? 1 and 2 are taken from my friend's Windows computer. He has the possibility to create the 'dropdown menu' I'm talking about by clicking on the little arrow next to the chapter.
I, on the other hand, don't have this arrow (image 3). Is this really a missing feature on the Mac version of OneNote or am I doing something wrong? I searched on some forums and no one seems to complain about that. Ok so apparently the term I was searching for was collapsible pages.
I wrote on the OneNote for Mac forum, and someone from Microsoft replied to me. Hi Sumasu, Collapsible pages feature is not available on OneNote for Mac 2016. The best way to improve the product is to submit your feedback and vote through the link below: Thanks for your understanding. Jerry I clicked on the link and I was shocked that the suggestion was shared in 2014! Microsoft didn't include this feature yet and it's 2016! What the hell Microsoft?! Anyway, I still voted for the suggestion and I hope that this feature will be included soon.