• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 576
  • Last Modified:

ctl click and jump to reference cell

Hi,

I'm just wondering how it's possible if I can simply do something like a CTRL+click on a cell and jump immediately to the referenced cell. For instance, this cell will reference another cell in some other worksheet or even another file.

Do I need some vb script or is this a built-in function in excel.

Thank you,
Victor
0
Victor Kimura
Asked:
Victor Kimura
  • 6
  • 5
  • 4
6 Solutions
 
SiddharthRoutCommented:
No Simply Right Click on the cell and click on 'Hyperlink' or press Ctl + K. See SnapShot.

Hope this helps

Sid

Untitled.jpg
0
 
Victor KimuraAuthor Commented:
Hi SiddharthRout,

When I click on CTRL+K I see the following: cell reference
But I wish to jump to the referenced cell which is KIMURACO!B296

Is there an easy keyboard shortcut?
0
 
SiddharthRoutCommented:
Yes that is fine. On the left hand side Click on "Place In this Document" and then select "KIMURACO". Please refer to the screenshot that I gave above :)

Sid
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Victor KimuraAuthor Commented:
Hi SiddharthRout,

I have hundreds of these and sometimes I need to change them dynamically. Is there an automated way of doing this?
0
 
SiddharthRoutCommented:
Yes a macro can be written.

If you give me exact details then maybe I would be able to help you.

Sid
0
 
dlmilleCommented:
I've actually developed a solution for exactly this.  Its a currently open question.  Just scroll down to my last post, and it has an example file:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26913378.html

you can download the file, called hyperlinkMacro-r2.xlsm and go to the tab called "3 Methods" to see 3 ways I setup to build hyperlinks dynamically.

One of the methods (Method 2) creates hyperlinks based on cell references.  First, run the button called "Remove All Hyperlinks on Sheet".  Then, look at the cell formulas in Method 2, and you'll see they are cell references.  Then, select one of those and hit the "Generate..." button to see the hyperlink created.

Method 3 probably suits even the best.  Just create a named range that includes all your cell references (blank cells are a-ok), run the macro and presto!  all the hyperlinks are generated.

Please, let me know if I can be of further assistance.

Enjoy!

Dave
0
 
Victor KimuraAuthor Commented:
Hi,

1) Okay, here are the details. I want to search all the cells in all the worksheets and find out if the cell is referencing another cell if so then hyperlink to it. So, in other words, Worksheet2!E1 is linking to Worksheet1!F4. I guess the problem may be complicated by the fact that the cell in Worksheet2!E1 may be more referencing more than one cell like it may also reference to Worksheet1!F5 as well if, for instance, Worksheet2!E1 = Worksheet1!F4+Worksheet1!F5.

2) I don't know if there is a reverse way to code this too. So, in other words, if there is a cell that is being referenced to it then I'd like to find out all the available cells that are linking to this cell. So if Worksheet1!A2 is linked to by Worksheet2!B5 and Worksheet3!R2 then when I go to Worksheet1!A2 I'd like to see them in like a pop-up menu or something or some organized and easy way to see what is linking to Worksheet1!A2.

Hope this makes sense. Thank you for your help.

0
 
dlmilleCommented:
It sounds more like a formula auditing problem.

Are you trying to debug a spreadsheet, or create hyperlinks?

Dave
0
 
SiddharthRoutCommented:
Yes that makes sense :)

It is definitely different that what you originally asked for :)

Give me some time and I will post a solution to this.

Sid
0
 
dlmilleCommented:
#1 is pretty easy, assuming you're ok with a direct cell reference

#2 is more a formulat auditing problem...  tutorial:  http://www.mrexcel.com/tip147.shtml

You can go to the Formulas tab on the ribbon, and trace precedents and/or dependents, with links to other worksheets, otherwise arrows pointing right at the references.

You can use this approach for #1 and #2.

If you desire the #1 approach, let me know, as the base code is embedded in my posted example, above.  otherwise, we can see what Sid comes up with.

Dave
0
 
Victor KimuraAuthor Commented:
Hi dlmille,

In reference to:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26913378.html
 with the second code that you gave.
I assigned a button to:
Sub GenerateNamesWithLinks()

But I'm not quite certain what it does. I didn't see any hyperlinks being created. I did notice that values were added to the named range in question. I guess I'm not quite certain what the values actually do in the named range. What is the purpose of that function?

The trace precedents and dependents help. Thanks for that info.
----------

Hi SiddharthRout,

Yes, sorry. I didn't initially think it was complicated but it seems more involved. I think the VB code from dlmille is good. If I can have hypertext links to multiple cells from a drop down menu that would be nice. I think the precedent and dependents is the functionality that I'm looking for but it seems like I can only do one cell at a time. Please post the code when you have time. =)


0
 
dlmilleCommented:
You might be more interested in the buttons on the demo sheet tab "3 Methods".

The function GenerateNamesWithLinks() was a test function to quickly create a list of range names with links built on top - you probably found that in the "Name Range" tab.

So, take a look at the Name Range Tab (it was actually a work in progress tab, but this is a good question and we can explore it).  Hit the Clear Named Range Hyperlink List button.  Then, hit the Generate Named Range list with Hyperlinks button.  If there were any named ranges in your spreadsheet with a link to once cell, then it would be listed/hyperlinked.

So, you have the 3 Methods (my recommendation to explore), and also this one other that I didn't mention, on the Name Range tab.

With this app, I believe the ONLY named range being created is the CONVERT_HYPERLINK name, which is utilized by the GenerateHyperlinksFromNamedRange() routine

Feel free to explore that a bit in the posted spreadsheet before determining how you'd like to proceed.

Enjoy!

Dave
0
 
dlmilleCommented:
To be clear:

1.  You can use the hyperlink formula (METHOD 1)
2.  You can select a cell and then hit a button to turn that cell into a hyperlink (METHOD 2)
3.  You can create a named range with any selection of cells in the active sheet, then hit a button to create hyperlinks on all those that are valid (METHOD 3)
4.  I guess you found another method:  You can generate a list of range names that exist, and hyperlinks would then be created on those names that are valid (new METHOD 4 :)

Creating drop-downs comment is vague to me and might scope to a new question.

Suggest you try these methods with the attached file and advise if you'd like further clarity.  Even if thise segue's into a new question set (re: drop down) it would be good to ensure the playing field is leveled before proceeding.

Dave
0
 
dlmilleCommented:
Here's that file, for easy reference.


Dave
hyperlinkMacro-r2.xlsm
0
 
Victor KimuraAuthor Commented:
Thanks dlmille, I thought I awarded the points awhile ago. Opps. Sorry.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 6
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now