Jump to a linked cell?

[uson]

Hi All,

I seem to remember a way to jump to a linked (not hyperlinked cell) but am having trouble. Hope you can help.

Here's the application. 12 months of sales data in worksheets - Jan11-Dec11 and a Summary worksheet that is linked to cells (MonthlySales) in each sheet - ie =Jan11!c5, =Feb11!c5 from the summary sheet..When working in the summary sheet how can I jump to the source worksheet - ie Jun11 - to see more details in that worksheet without tabbing through the sheets? Thought there was a Ctrl?? of F?? to do that.

Thanks so much.

 

[JoeMo]

• Select the cell you want on the summary sheet.
• Go to the Formulas menu and select Trace Precedents
• double click the arrow connected to the mini sheet that appears

remove the mini sheet by Formulas/Remove Arrows

 

[uson]

JoeMo - thanks for the quick response and that feature works. But, I was looking for a "one-click" solution. The desire is when the summary worksheet is being reviewed, the inevitable questions like "How many orders in January" is explained in the January sheet. What's desired is a one-click way from that summary cell to jump to the January detail sheet. Maybe a macro etc that does the steps you described.

Thanks

 

[JoeMo]

JoeMo - thanks for the quick response and that feature works. But, I was looking for a "one-click" solution. The desire is when the summary worksheet is being reviewed, the inevitable questions like "How many orders in January" is explained in the January sheet. What's desired is a one-click way from that summary cell to jump to the January detail sheet. Maybe a macro etc that does the steps you described.

Thanks

Assuming your summary formulas look like =Jan!F8 try using this code when you have selected a cell and you want to go to its precedent. You can put a button on the summary sheet or put the code in your PERSONAL macro book and place a button on the tool bar.

Sub JumpTo()
Dim sh As String, rng As String
With Selection
 sh = Mid(.Formula, 2, InStr(1, .Formula, "!") - 2)
 rng = Mid(.Formula, InStr(1, .Formula, "!") + 1)
End With
Application.Goto Sheets(sh).Range(rng)
End Sub

 

[jrw1]

USON -


There is a shortcut to solve this, no need for macros. I believe the shortcut you are looking for is Ctrl + "[" - that's Control and Open Left Bracket - this is the keyboard shortcut for "Go to dependent cell"

Hope that helps.

 

[uson]

Thanks - the Control [ works great. I knew there was a shortcut. Any idea about a reverse shortcut - once I switch to the detail worksheet is there a Control ??? to follow link back to the summary sheet?

 

[jrw1]

Yes - its just the other bracket. Ctrl + [ goes to the precedent cell, Ctrl + ] goes to the first dependent cell (I think - not sure on the order).

 

[uson]

Thanks for all the help.

As jrw1 suggested the Control [ works great to jump to the dependent cell, but Control ] does not work as the back shortcut for me. So I used the code from JoeMo and added a home feature that goes back to the worksheet summary page. Here's what I use - hope it helps you..

Set up two macro modules, in the first place this code

Sub JumpTo()
Dim sh As String, rng As String
With Selection
sh = Mid(.Formula, 2, InStr(1, .Formula, "!") - 2)
rng = Mid(.Formula, InStr(1, .Formula, "!") + 1)
Application.Goto Sheets(sh).Range(rng)
End With
End Sub

and in the second place this code

Sub Home()
Worksheets("THE SUMMARY PAGE").Select
End Sub

Then I created short keys for each so as I am working I can Ctrl+Shift J to jump to the linked cell then Ctrl+Shift H to return to the home page. Works great.