Cross validating drop down menu
Select a cell or a range you want to place the drop down list, and click Data Data Validation. In the Data Validation dialog, under Settings tab, select List from Allow section, and the select the symbol cells into Source textbox.
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.
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.
So below I want the person to see the tick and cross in the data validation dropdown AND I want the cell in C7 to change depending on whether they use a tick (✓) or a cross (✗) It is surprisingly easy. So you can copy a tick (highlight the one below and click CTRL and C).
✓ then go into Excel, click on the relevant cell and: You need to repeat the same thing with a cross which you put after the tick with a comma before it (bit tedious but can make it a bit faster with clever use of the clipboard).
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.This formula still uses INDIRECT to link the text value in A1 to a named range, but before INDIRECT runs, the SUBSTITUTE function replaces all spaces with underscores.