Friday, February 19, 2010

Linking to places in Excel

Much like you can link from a table of content in word to the corresponding title in the document, I wanted to link an Excel cell to another cell inside the document (for example to the cell A5). The problem is that if a new cell is inserted above the destination cell (changing it from A5 to A6 for example), the original link doesn't change the destination cell's number (it stays to A5).

So I decided to use the "name range" possibility. But as explained here, "If the cell or range already has a name you may type a new name in the name box and it will be accepted as a name for that cell or range as long as it is not a name that is currently used elsewhere. However, the first name given will still exist and will still be the one shown in the name box when you select the cell or range. In formulas etc. You will still be able to use both the new name and the old name. "
Here (in 2004) they proposed to use a VBA function, but that is actually not nessecary.

The solution is to use the "Name Manager" dialog box. From Excel help: "Use the Name Manager dialog box to work with all of the defined names and table names in the workbook. For example, you may want to find names with errors, confirm the value and reference of a name, view or edit descriptive comments, or determine the scope. You can also sort and filter the list of names, and easily add, change, or delete names from one location.

To open the Name Manager dialog box, on the Formula tab, in the Defined Names group, click Name Manager."

And the way I solved my problem was by using the "create from selection" dialog in the "defined names" area of the formula tab.

No comments: