Forum Discussion
How do I link a cell to another sheet in the same workbook, allowing me to view the sheet?
- Hello,
You need to insert a shape into each of the sheet tabs and apply hyperlink to each of the inserted shapes.
Steps
1. In the Data sheet, Insert desired shape
2. Type in name inside the shape
3. Right click and select Link
4. Select Place in this Document
5. Under the Cell Reference, click on the Sheet tab you wanna link to (for this example, select Report)
Click OK
6. Click away to deselect the shape
7. In the Report sheet, insert desired shape
8. Name the shape as in step 2 above
9. Right-click and select Link
10 Select Place in this Document
11. Under the Cell Reference, select Data
Click OK.
12. Click away to deselect the shape
You're going to see a shape of hand whenever you hover the mouse to the shape. And when you click on Report Sheet tab shape, it will move you to the Data Sheet tab
You can do the same for other sheet tabs. This will make it easier to navigate from one sheet to another.
See the pictures attached
We have a live information tracker that has everything all in one file. As the data is no longer needed, it's removed from the file and master list.
EG: Item | Order Number (also the link to worksheet for this order)
Using CTRL-K to create a link within the document is applied to the cell itself, which breaks when the data is sorted or moved. Though you don't know this happened until you click on the on the cell because the default hyperlink formatting remains intact when the link is broken/removed.
And using the 'sheet'!cell reference Excel autofills when you start the formula and click on the cell in the other worksheet that you want linked doesn't work at all.
EG: =HYPERLINK('Sheet2'!A1,"visible_text")
Using this template creates a version that can be sorted and cut or copy/pasted without losing functionality or the need for anchors.
Template:
=HYPERLINK("#'Sheet2'"&"!$A$1","Visible_Text")
Applied:
=HYPERLINK("#'PO_7'"&"!$A$1","PO 7")
(Note the single-quotes before and after the sheet name. They're hard to spot, and the formula doesn't work without them)
For my needs, I always include the $s to maintain the hard-lock onto a specific cell within the sheet as the formulas get resorted within our list.
Remove them, as applicable, to suit your personal usage.