Excel: Table of contents
So here is the promised first real post to the Excel-wizardry category, delayed by a problem found with the hint!
Goal: Create a series of hyperlinks which link to specific cells on other sheets in the workbook. The cell references should be ‘live’ in the sense that copying over a range should produce modified relative references to make setup and maintenance easier
Solution: =HYPERLINK(CELL(“address”
Description: The CELL function returns information about a cell reference – in this case its “address”. This reference is then passed as the first argument of the HYPERLINK function to determine the target. This must be done because a direct reference to the cell in question is not treated as ‘live’ and therefore when copied does not adjust relative references. The second argument references the same cell directly to use the contents as the text of the hyperlink.
Caveat: Because of the way the HYPERLINK function works, the file must be on disk for this to work. The upshot of this is if someone sends you this file as an attachment to an email you must detach and save it first, then open it from disk. If you launch the attachment directly from the email, a temporary file is created and the function does not correctly link.
