On sheet 1 of my workbook Column A contains 400+ unique values. Each of these values has a worksheet within the workbook that shares the same name.
For example, cell A2 on Sheet1 could be called 'Tiger' and cell A3 could be called 'Bear'. I need to create a hyperlink for each 'Tiger' and 'Bear' to cell A1 of a worksheet of the same name.
I understand how to create add a hyperlink on a cell by cell basis. I am hoping that there is a more efficient way to complete this task. Is is possible?
In A2 cell
sheet2
In B2 cell
Formula:
Please Login or Register to view this content.
Drag it down.....
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Thank you for the reply. Is there now a way to remove the formula, but maintain the hyperlink? I would really prefer to not have 2 columns of data.
May be you should go for VBA code solution
• Select the workbook you want to contain the macro
• ALT+F11...to open the VBA editor
• Insert.Module
• Copy the below code and paste it into that module:
• Select the range of cells you want converted to hyperlinks
Please Login or Register to view this content.
• ALT+F8...to see the list of available macros
...Select: ConvcertTxt2Hyperlinks()
...Click: Run
Assuming each cell contains the name of a worksheet,
each selected cell will now contain a hyperlink to cell A1 of the referenced sheet.
Is that something you can work with?
Thank you for the response. The code did put a hyperlink in all cells. However, they do not work; "Cannot open the specified file". While placing the cursor over the link it shows the file path correctly, but does not show the file name like it does if I were to do it manually. It appears to be attempting to open a workbook with that cell's name instead of a worksheet.
I assumed, perhaps wrongly, that all of the tabs were in the workbook that contains the links. If they are in other workbooks, different code is necessary. Can you post some samples of actual cell contents?
You did not assume incorrectly. All sheets are contained with the same worksheet. I am going to PM you the link to the worksheet. The first sheet is the one that need the hyperlinks (Column A) to the corresponding sheet within the workbook.
I'm sorry....I posted the wrong vba code!
This is what I intended to post:
Does that work better?Please Login or Register to view this content.
Note: You'll probably need to change the Hyperlink Style to make the font color a bit easier on the eyes.
Perfect!! Thank you very much for your help!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks