I use a VLOOKUP formula to search a column of over 10,000 cells to find a string and output the cell address into a fixed cell. How can I create a button that will read the address in the fixed cell and jump to that cell?
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Jump to cell
0
Answers
0
Selected Answer
After a frustrating day of hunting, trial and error I found this:
Link Within the Same Excel File
From http://www.contextures.com/excelhyperlinkfunction.html#videoformula
:
=HYPERLINK("#"&"'"&J10&"'!"&I10,"Go To Cell "&I10)
The sheet name is given in Cell J10
The jump to cell is given in Cell I10
The appearance of the Hyperlink in the workbook is "Go To Cell I10
Discussion
With my formula, you should not have needed to include the full file reference, as you mentioned in the comments.
That said, thanks for posting what worked for you and make sure to mark this as the answer then you can!
That said, thanks for posting what worked for you and make sure to mark this as the answer then you can!
don (rep: 1989) Feb 25, '17 at 7:33 am
0
You don't need a macro for this. You can use a formula like this:
=HYPERLINK("[Book1]Sheet1!"&A2,"Go to Record")Book1 is the name of the current workbook.
Sheet1 is the name of the worksheet where you want to go.
A2 is the cell that contains the cell reference for the result. You could also just put the formula that got that result in place of this cell reference.
Go to Record is the text that will appear as the link.
Discussion
I tried what you suggested but it gave me the following message: "An unexpedted error has ocurred."
Here is what I used:
=HYPERLINK("[C:\Users\George\Documents\Velon Project\Alphabetic Listing By Book No\Combined Hymn List.xlxs]Hymns!" &K7, "Go to Row " & K7)
The workbook and sheet specified above are actually the current workbook and sheet. K7 is a cell where the fillowing text is displayed: B108.
The hyperlink resolves to the following:
[C:\Users\George\Documents\Velon Project\Alphabetic Listing By Book No\Combined Hymn List.xlxs]Hymns!B108
The cell where this hyperlink is written displays the following:
Go to Row B108.
Can you offer any further suggestions? Thanks
Here is what I used:
=HYPERLINK("[C:\Users\George\Documents\Velon Project\Alphabetic Listing By Book No\Combined Hymn List.xlxs]Hymns!" &K7, "Go to Row " & K7)
The workbook and sheet specified above are actually the current workbook and sheet. K7 is a cell where the fillowing text is displayed: B108.
The hyperlink resolves to the following:
[C:\Users\George\Documents\Velon Project\Alphabetic Listing By Book No\Combined Hymn List.xlxs]Hymns!B108
The cell where this hyperlink is written displays the following:
Go to Row B108.
Can you offer any further suggestions? Thanks
Georgestef Feb 24, '17 at 1:18 pm
After a day of hunting and trying different things I finally found this:
Link Within the Same Excel File From http://www.contextures.com/excelhyperlinkfunction.html#videoformula : =HYPERLINK("#"&"'"&J10&"'!"&I10,"Go To Cell "&I10) The sheet name is given in Cell J10 The jump to cell is given in Cell I10 The appearance of the Hyperlink in the workbook is "Go To Cell I10
Link Within the Same Excel File From http://www.contextures.com/excelhyperlinkfunction.html#videoformula : =HYPERLINK("#"&"'"&J10&"'!"&I10,"Go To Cell "&I10) The sheet name is given in Cell J10 The jump to cell is given in Cell I10 The appearance of the Hyperlink in the workbook is "Go To Cell I10
Georgestef Feb 24, '17 at 3:19 pm