Jul 29 2019 10:26 AM - edited Jul 29 2019 10:28 AM
So I have an index that links to other sheets. But it will name the link '1'. I am not sure what i need to do, so that the hyperlink simply copies the text in a specific cell, which in this case will always be in cell A1, which would say something like "dining room" or "Bedroom" (or something along those lines). Any help with a lot of explanation would be helpful.. Also, if it is possible, can the link change if the referenced text changs?
Thanks!
Jul 29 2019 10:39 AM
Hi,
It would be good if you share the excel file.
Secondly, using If() function a link can be changed if the referenced text changes.
Thanks,
Tauqeer
Jul 29 2019 11:13 AM
@tauqeeracma I cant share the file itself, but I made a simplified version (that doesnt have any real info) of what I am essentially trying to do...
Youll see on the index page, that I have a room number in the first column, and a hyperlink in the second. I am trying to get the hyperlink text to be the same as the text on the linked cell (and should it change, auto update), without having to manually write each one.
Do you think that is possible? Thanks for the help!
Jul 29 2019 11:27 AM
Jul 29 2019 11:32 AM
@tauqeeracma woops, forgot to attach last time...
Jul 29 2019 11:49 AM
Jul 29 2019 11:55 AM
@tauqeeracma This is great! Exactly what I am looking for. Could you please explain how to do this?
Thanks for the help
Jul 29 2019 12:10 PM
Hi
You can create a DYNAMIC Hyperlink that changes based on cell contents.
To do this,
You need to create a "Hyperlink Function"
The first argument of the Hyperlink Function is a cell reference to a destination.
The second argument of the "Hyperlink Function" is a User Friendly Name
I am attaching here with a link to one of my tutorials in which I explain this function in details (you can jump to minute 9:00 of the Tutorial)
https://www.youtube.com/watch?v=FeKgWSD8IWc
hope that helps
Nabil Mourad
Jul 29 2019 12:16 PM
@mbartolucci Its quite simple, just follow the steps in the attached PDF. Thanks
Jul 31 2019 05:39 AM
@tauqeeracma Im sorry, I know i am being dense, but I dont understand that last step. After I have entered the '=' sign, I must go to the other sheet (0001 in this case), but once I am there, how do I link it? Ive been trying for a while now, and I cant get it to work...
Jul 31 2019 05:45 AM
@nabilmourad Thanks for the help! Unfortunately I am still having trouble figuring this out... I dont understand what each component of that structure does... Could you please break down each part for me? As I am sure you have noticed, I am not very knowledgeable in Excel.... So from my understanding, my hyperlink should look something like this: '=HYPERLINK("#"'&A2&"'!A1",A2)
what is the # sign for?
Thanks!
Jul 31 2019 05:50 AM
No issues
Before entering '=' sign, please select the text in formula bar and then press = and simply make a link to other sheet.
Jul 31 2019 05:58 AM
Hi
It's obvious you have a problem with the position of the Single quotation and the double quotations.
Also tell me what do you have in Cell A1, Cell A2 and in which cell you write the formula?
You need to follow this pattern.
If I had a file I would do it for you (or look at my tutorial for this part)
Nabil Mourad
Jul 31 2019 06:11 AM
@tauqeeracma ok, so once I have the =, do I hit ctrl+k to make the link? that doesnt seem to work for me...
Jul 31 2019 06:16 AM - edited Jul 31 2019 06:17 AM
@nabilmourad I posted a basic excel file of what I am essentially trying to do. So on my index page Column B is where I want to attach the link (with friendly name) to cell A1 from every page except the index (which contains a room name in that cell).
Jul 31 2019 06:19 AM - edited Jul 31 2019 06:33 AM
Hi
I understand your confusion because you have 2 contributors replying to you using two completely different concepts:
The CTRL + K for inserting a hyperlink is a STATIC technique used when you need just one or 2 links >> I am not using this technique.
However
The technique I am showing you is a DYNAMIC technique that enables you to create a "HYPERLINK" formula that can be copied to thousands of cells
Please do not mix the instructions from different contributors when they discuss different options.
Nabil Mourad
Jul 31 2019 06:24 AM - edited Jul 31 2019 06:26 AM
Hi
I just looked at your sample and here is the function
Copy and Paste in Cell B1
assuming that you have in Column A starting from A2:
0001,0002,0003...
=HYPERLINK("#'"&A2&"'!A1",A2)
Hope that helps
Nabil Mourad
Jul 31 2019 06:31 AM
Jul 31 2019 06:59 AM
@nabilmourad Oh I think I understand the problem. It is linking to the cell beside it correct?
So in the file, under 'index', i have a room number which corresponds with a tab. so for example, in the index, I want to have the cell beside room number 0004 (under Room Name column, B) to link to tab 0004, and to name itself with whatever is written in cell A1 of sheet 0004. Perhaps thats what you are already saying and I simply dont understand..
Jul 31 2019 07:07 AM