It sounds simple, but no one knows the answer! HOW TO CREATE A HYPERLINK USING LITTLE INFORMATION

[pedromrnovaes]

The answer to this question would save HOURS of work every week of my life. So thank you!!

The case is, I need to creat a hyperlink to another workbook using only the ID of a client. The ID is a 4 digit number (like 2343) and the adress is always "C:\\Users\samsung\FICHA-2343.xlsx".Only changing the "2343".

Everyday we have to register the ID in one column of the "Clients" table (the B column) and then create a hyperlink in another column (the C column) using the old way, Ctrl+k and selecting the file. I'm talking hundreds of clients every week. This takes a lot of time. Generating a hyperlink automatically based on the ID would be AWESOME.

But how do I do that?? It seems "doable".

Here is my attempt at resolving this:

My solution so far (probably a terrible one) was, outside of the table, creating a column that reffered to the "ID" column but with a custom fromat so it appeared as "FICHA-xxxx.xlsx" (but only with formatting, not changing the value, I don't know how to do that). Than I would put "C:\\Users\samsung\" in another cell and finally use the HYPERLINK function to creat a link based on that. It would look like this (Lets say G1 had the ID formatted in the right way (as FICHA-xxxx.xlsx) and F5 had "C:\\Users\samsung\):

=HYPERLINK(B1&F5)

BUT it didn't work at all. The result was "C:\\Users\samsung\2343", meaning that excel is not referring to the formatted cell as she looks, formated. It's only looking at it's value.



I could really use some help. Thanks guys.

 

[admiral100]

Hi,

try this

=HYPERLINK("C:\\Users\samsung\"&"FICHA-"&B1&".xlsx")

 

[pedromrnovaes]

You're a god**** genious. That WORKED! I get impressed by excel every single time... Thank you man, that will save us not only time but a lot of money too. Thank you very much, from Brazil!!!

 

[admiral100]

You are welcome ,
I'm certainly not a genius , not a complicated formula ... thanks for the kind words
I was glad I could help you

 

[pedromrnovaes]

You are welcome ,
I'm certainly not a genius , not a complicated formula ... thanks for the kind words
I was glad I could help you

Could I use your knowledge on another thing?

There's two steps on registering new clients that really take a lot of our time: creating a hyperlink (which you completely solved) and creating a reference to a cell in another workbook. We make a reference to a cell that gives us the status of our clients, if they are "active" or "inactive". The picture below can make that more easy to see.

What makes it easier for us is the fact that the cell reference is almost equal every single time: "C:\Users\samsung\[FICHA-xxxx.xlsx]Aluno'!$B$2 - The xxxx representing the ID number. So we don't need to actually make the reference every time, but we still have to copy, paste and change the number on the link to the number on the person's ID. That takes a quite good amount of time when we're talking about hundreds of people.

My solution so far was trying this: ="='C:\Users\samsung\Google Dirve\[FICHA-"&B4&".xlsx]Aluno'!$B$2"

B4 representing one cell that contained an ID. That doesn't work, because excel doesn't reconize as a formula, but as a text. How do I get this to be automated? If you are able to answer that, I think you'd be employee of the month on our business hahahaha

 

[admiral100]

Hi,

If I understand you ....

=HYPERLINK("C:\Users\samsung\Google Dirve\FICHA-"&INDEX(Aluno!B2:B6,MATCH(B4,Aluno!B2:B26,0))&".xlsx")

 

[admiral100]

anoher way

=HYPERLINK("C:\Users\samsung\Google Dirve\FICHA-"&INDIRECT("Aluno!B4")&".xlsx")