Perhaps this is an Excel 'trick' that others know of already but I will share none-the-less!
I spent a little time figuring this one out and wanted to share the solution to this nifty feature of Excel. I have a spreadsheet with multiple worksheets and on the Test Cases worksheet, I wanted the cell contents to be a clickable link/reference to a related cell on the Design worksheet to tie in a specific test case - to the original programming change required.
This is where the HYPERLINK function comes in very handy - it can be used to hyperlink to not only an external file or URL but also to a clickable cell reference, which is exactly what I wanted. The two parameters are the reference to the cell and an optional "Friendly Name" . The trick is to specify the full worksheet & cell address in the first parameter, enclosing with double quotes and preceding with the # symbol. The second parameter, the friendly name - is what will be displayed in source cell, if you don't want technical gobbley-gook displayed. You'll see from the two screenshots below, that I have a formula in cell B3 of the Test Cases worksheet and by specifying the "#Design!B3" as the first parameter, I've created a nifty link so that when the user clicks on it, it brings them to the related cell in the "Design" worksheet.
 |
| Test Cases Worksheet with Link to Related Cell in the Design Worksheet |
|
| Design Worksheet Referenced by Clickable Link in the Test Cases Worksheet |
