Unit 19: Creating Name Range

The problem with this feature is that even if you hide the list in AZ1:AZ10, there’s the chance that someone will inadvertently ruin your list by deleting rows 5 and 7. It would be much better if you could store the list on a hidden worksheet, but Microsoft says that the list must be on the current worksheet. You can work around this annoying limitation by storing the list on another worksheet and naming the list. For example:

  • Insert a new worksheet named Lists.

  • Type your list in A1:A10 on the Lists worksheet.

  • Name that range with the name RepList.

  • Optionally, hide the Lists worksheet. For Excel 2003, go to Format, Sheet, Hide. In Excel 2007, right-click on the worksheet tab and select Hide.

  • Back on the original worksheet, choose Data, Validation, and thenchange the Allow box to List. In the List box, type =RepList and press Enter. Excel will now use the list from the hidden worksheet to populate the dropdown in the cell. As Bookmarks Names make great bookmarks in large
    workbooks. Rather than navigating from Sheet1 to Sheet25 by using Ctrl+PgDn 24 times, you can define a named range on Sheet1 & another named range on Sheet25. Open the Name Box dropdown, and choose the appropriate name to jump to the named cell on worksheet. For Storing
    a Constant A name doesn’t have to refer to a cell. You can create name such as Sales Tax and store a constant such as 0.065 to that name. To do this, you’ll have to use the Names dialog box

UNIT 19 – Creating Name Range

There aren't any posts currently published in this category.