An easy way to quickly reference a cell or list of cells is to define them with a named range.
To do this, simply select a single cell or a range of contiguous cells, then click the name box next to the formula bar and type the name you wish to assign to those cells and press Enter. Your name must begin with a letter and can’t have any spaces (an underscore “_” is a good substitute for a space).
You can use your named range in several different ways. In the examples below, assume that you have a list of customers in cells A1 through A5, as follows: A1 – Joe Smith, A2 – Jane Doe, A3 – Sam Jones, A4 – Sue Kim and A5 – Mike Rodriguez, that you have named “customers”.
- Formula: You can click in another cell and type =customers. The result will be whatever value is in the named range in the corresponding row. So if you type =customers in cell B2, the result will be Jane Doe, because that is in the corresponding row. The same would be true if your list spanned across several columns.
- Data Validation: If you want to have the value of a particular cell only be a value that is in your list, you can set the Data Validation to only allow those values. Click Data | Validation, then on the Settings tab, select Allow | List, and in the source type =customers. If you check the “In-cell dropdown” box, anytime you click that cell, there will be a little drop down arrow that will allow you to select the values from your list. This list can be placed anywhere in your workbook, and unlike the previous example, has no regard for which row you place the list in.