You are here:

Excel/hyperlink question

Advertisement


Question
Hi Bob,

I have a hyperlinking question although I'm fairly familiar with hyperlinking within excel workbooks.  I have a single spreadsheet workbook and I am trying to jump to a new area of that workbook using a hyperlink.  I have been building a database over time and now have a few hundred rows worth of data.  The information I enter is in row form - so I add data along a 'new' row for each data set (below the previously existing data).  As the data is entered, I need to jump to different parts of the worksheet (same row but different columns) occasionally as I enter the data.  Once you have a certain number of rows of data, it no longer helps to hyperlink to cell X1, for example, as the row you are working on is in row 200, so the hyperlink becomes useless - unless you continually update it to link to a row within the row range currently visible on the monitor when you hit the hyperlink.  Do you happen to know of a way to create a hyperlink that simply shoots you to another column but allows you to stay on the same row you have been working in?  This is really what I'm looking for and haven't been able to find anything that touches on this topic specifically as I'm sure it is an infrequent problem...

Any help would be appreciated.

Thank you,

Jason

Answer
Instead of using hyperlinks, try using selection change events (VBA)
For example, this code will move to column X whenever you click in column A:
right-click the sheet tab, select View Code, enter:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Target.Column = 1 Then Cells(Target.Row, 24).Select
End Sub

But, you'll never be able to select anything in column A that way!!
Maybe a double-click:
This code will take you to column X of the same row whenever you double-click in ANY cell:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Cells(Target.Row, 24).Select
End Sub
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Bob Umlas

Expertise

I`m a Microsoft Excel MVP (Most Valuable Professional) and have been since the inception of the program in 1995. I can answer every kind of Excel question except: API, Importing/exporting to other programs (powerpoint, word,...) Also check out my in-person training link at http://www.thumbtack.com/ny/new-york/excel-training/

Experience

Worked with MS Excel since version 0.99 (on the Mac!). Was contributing editor to Excellence Magazine, having written >300 articles. John Walkenbach said of me "I finally met someone who knows as much about Excel as I do."

Publications
Excellence, The Expert, Microsoft

Education/Credentials
BA in math, Hofstra University, 1965

Awards and Honors
MVP
Led sessions for the Convergence 2004-2006 seminar on Excel tips & tricks

©2016 About.com. All rights reserved.