If you inherit a spreadsheet from someone else and want to see what block names (if any) exist within the worksheet, you can have Excel paste a list of block names within the current spreadsheet.
Excel will create a two-column table that alphabetically lists the defined block names in the current file and their corresponding addresses.
Before continuing, move to the location within the worksheet where you would like the list to be created. The list will occupy two columns and as many rows as there are block names. I would suggest you paste the list on a sheet of its own and then Hide the sheet. That will keep the list safe for use later, if needed (see the blog post Oh, NO! I just deleted a name that was used in a formula).
Be careful! If data is located in the cells where the list is being created, Excel will overwrite the existing data without warning.
- Click on Use in Formula (located within the Defined Names section on the Formula Ribbon).
- From the pull-down list, select Paste Names…
The following dialog box will be displayed:
- Click on the Past List button. Excel pastes a list similar to the one shown below. The first column lists the block names while the second column lists the cell addresses.
Lists do not automatically update themselves. If, for some reason, you add a new named block or change an existing one, Excel will not automatically update the list within your spreadsheet. Instead, you will need to re-paste the list each time a change is made.