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.
Adding a drop down list to your worksheet allows the users of the worksheet to enter information in a neat, consistent format.
For example, say your worksheet asks a user to input information that later will get summarized in a PivotTable. If the user doesn’t enter the information consistently (i.e. spells out words some of the time, and abbreviates the rest), the information won’t show correctly in the PivotTable output. Adding the drop down list will limit the number of values a user can enter into a cell, and provide consistency to your worksheet.
To do this, follow the steps below.
- Select the cell where you want your custom list to appear (if you want the same list to appear in multiple cells, you can copy the list later).
- Click Data | Validation.
- On the Data Validation window’s Settings tab, select Allow: List, and make sure the In-cell dropdown box is checked.
- In the Source box, type the items you want in the drop down list separated by a comma. Alternatively, you can refer to a range of cells in the same worksheet that contains your list, or refer to a named range elsewhere in your workbook.
- Click OK.
When you select the cell that now contains the data validation, you should see an arrow to the right of the cell. Click the arrow, to display the drop down list.
Say you have a spreadsheet that keeps track of weekly sales information, which you want displayed in a new sheet for each month. You don’t want to copy an “original” worksheet each week, so a macro can help to automate this process.
The macro code below will copy the worksheet named “Original”, and place it after the last worksheet in your file. It will then prompt you to enter in the date that corresponds with that week. It will continue to do so until you enter a valid worksheet name (i.e. valid format and a name that isn’t already used somewhere else in the workbook. Make sure your workbook already has a file called “Original”, and it is formatted the way you want all your weekly reports to be formatted. (Note if your “Original” worksheet isn’t called “Original”, you can change the name in the macro code below to whatever you want).
Copy all the code below. Paste it into your workbook’s Visual Basic editor, either under a Microsoft Excel Object or Module.
Need help? Use our nifty guide to help figure out how to install and use your macros.
Sub CopyNewSheet()
'
'MACROS BY EXCELZOOM.COM
'
Dim shname As String
Dim wrksh As Worksheet
Worksheets("Original").Copy after:=Sheets(Worksheets.Count)
Set wrksh = ActiveSheet
Do While shname <> wrksh.Name
shname = Application.InputBox _
(Prompt:="Enter This Week's Date")
On Error Resume Next
wrksh.Name = shname
On Error GoTo 0
Loop
Set wrksh = Nothing
End Sub