Pro Tip: use Data Validation to provide a user with a list of choices
One of the more common tasks when using Excel either as a database or a front end for analysis is limiting the choices of the user. Sometimes this is done because you want to avoid errors. Other times, you can provide a list of choices based on the current state of the sparedsheet. One of the most common tasks in this arena is to make it simpler for the user and avoid typing some value into a cell. Maybe you provide an interface to plot 1 of several columns. The easiest way to ask the user for their choice is to type in the header vale of the column to plot. You can use a dropdown to limit the user’s options to only those that are valid.
You can use the Data->Data Validation tools to provide a drop down of choices. In that menu, choose List as the source data. From there, you have two choices for how to define the list:
THe former option is easiest if you know the region of cells will be a fixed size and is located on the current sheet. There is no good reason that Excel limits you to the current sheet here, but realize it is a real limit. To get around it, you can use a Named Range.
For a Named Range, you will use this for 2 reasons:
Option 2 is quite powerful because it means that you can provide the user with a list of choices that adapt to the current state of the sheet. This is great when the list of headers can change size. Or maybe you want to allow them to select any unique value from a column. In either case, you can whip up a Named Range with all those good dynamic references (e.g. use INDEX somewhere). You can also use an array formula 1.
1 Note that Named Ranges will always evaluate as array formulas so you don’t have to enter with CTRL+SHIFT+ENTER (nor can you).