Hyperlink to hyperlink

[Digidre]

I have table of 1000 records. In this table I collect hyperlinks to a lot of Google Drive files. Those links are made manually.
In another sheet I created a lot of fields where I find the info out of my database (vlookup). Works fine of course.

But... I also want to find some of the fields with the hyperlinks, and display the very same hyperlinks.
Here is were the problem arises. The link that is displayed will lead me to .... my 1000 record database, and not to the Google Drive file.

How do I solve that?

Looking forward to any answers.

Regards,
André

[kev_]

I have not tried with google drive, but this method should work - see attached file
The "green" cell on sheet2 contains the formula below and that links successfully to the hyperlink in cell C2 in sheet1
(I hyperlinked to file on my D drive)

Please Login or Register  to view this content.

EXPLANATION

The actual link is in cell C2 on sheet1

Above formula creates a working link to the same link
- the lookup value is B2 (sheet2)
- that is looked up (using MATCH) in columnA in sheet1 (to give the row number = 2)
- the link is in column C = column 3)
- ADDRESS(row 2, column 3) = C2
- then add the sheet reference "Sheet1!&ADDRESS...
- then wrap with INDIRECT
- which provides what is required inside the HYPERLINK formula

NOTE - the cell containing the link in sheet1 should not contain a "friendly name" (full link path is required)

I have included workings below row 2 on sheet2, so that you can see how the formula builds - those workings are not required

[Digidre]

Thank you kev_ !!
I tried to apply the formula and I think I'm really close.

The combination Address & Match brings up the cell in the tab that I'm looking for, so that's working fine.
The destination cell is also recognized as a link, for the "hand"-cursor shows up.

The last stap is to solve this message I get:
"The file you are looking for can not be opened". Obviously my message is in Dutch, so I translated it to English.

The message is kinda odd, because the pdf is accessible (with the regular link I can open it from the cell).
Do you have any suggestions?

Thank you!
André

[kev_]

Hi André
Excel is not always very helpful! It is probably looking in the wrong place for the file - but it does not tell us where it is looking

I have just tested my method works fine with a PDF. Let's get it working on your local drive first, and then you can make it work with other drives.

Please reply with
- the full path
- and file name
- of a real PDF file which is on your local C drive. Something like this:

C:\Documents\TestFolder\myFile.pdf

I will create the same folder here and a file with the same name, and send you a workbook with the formula that works for me using the method in my previous post

thanks
Kev

[Digidre]

Hi Kev,

I created a folder with the following pad/name:
C:\Users\HP\Documents\TESTFOLDER

The name of the file is:
0707 OPW LS - Met diep ontzag

I noticed that Excel allows me to create a hyperlink manually, and it will let me select the file in the path I mentioned above.
It does replace all the spaces with a lot of rubbish:

..\Documents\TESTFOLDER\0707%20OPW%20LS%20-%20Met%20diep%20ontzag.pdf

Would that mess up the procedure?
I did try the hyperlink with the formula and the TESTFOLDER, but I still got the same error message.

Regards,
André

[sandy666]

maybe try this :
=HYPERLINK("[C:\Users\HP\Documents\TESTFOLDER\0707 OPW LS - Met diep ontzag.pdf]","any tooltip")

[Digidre]

Hi Sandy,

For sure: it opened the file! So a hyperlink pointing to a local file is no problem.

Regards,
André

[sandy666]

So yuou need something like this?
=HYPERLINK("https://drive.google.com/file/d/0B9gVfJJNXtlub2xJbzlRM3AwbTg/view?usp=sharing","vx")

both files are on GD

[sandy666]

You said : can't open file
paste here yours hyperlink with this "error"
and what are you trying to do? open local file from local excel, open local file from google drve excel (you will need server on your computer), open file from GD with local excel file?

[Digidre]

This is the issue: I need to link my hyperlink to a cell that has the link to the PDF in my Google Drive.
So, the last cell has the a link similar to what you mention.

We need the Hyperlink-formula that finds the last cell with Address & Match.

[kev_]

as promised here is file with method working with your file path and file name

These 3 ways of creating hyperlink all work in the file:

with and without [ ] brackets

Please Login or Register  to view this content.

and type link directly into cell

Please Login or Register  to view this content.

and all 3 links to links also work

[Digidre]

This is the formula in my destination cell:
=HYPERLINK(INDIRECT("BRON!"&ADRES(VERGELIJKEN($D$2;BRON!$A:$A;0);8;4)))

[sandy666]

This is the issue: I need to link my hyperlink to a cell that has the link to the PDF in my Google Drive.

File in post #2
change link on sheet1 C2 to GD link
if you click on sheet2 C2 it should open defined file from GD

you will not see link on sheet1 C2 because link from sheet2 C2 goes indirectly (thru the second link) to file on GD

or I really misunderstood

[kev_]

@sandy666 - I am staying out of this conversation - I do not want to confuse things - OP asked for link via link which is why it goes thru 2nd link

[sandy666]

In post #2 you did it and it works. Now OP should change "mid" link to GD link document and that's all .
All is work well.

Or OP should be more descriptive with details

[sandy666]

Here is:
Excel file with link selection to links to files (local or online)

is that what you want to achieve?

see att.

[dallen442]

Great news Sandy, it works like a charm. After my coffee I will try to figure out what i was doing wrong in my formula but the sample was easy enough to adapt to fix my problem. Thank You.

[sandy666]

You are welcome

If problem is resolved read my "dancing" footnote

[dallen442]

Thanks again for the help. The sample you sent looks almost like one of the formulas i wrote but could not get to work at all. I compared the details and discovered I was trying to match the entire data table instead of just the column the number was in. After changing the code to just look in one column it worked great. Now I will look for your dancing footnote. I am new to forums so navigating around them is not so easy for an old dog.

[sandy666]

@ dallen442 if you dont know "How to..." look at:


Go to the bottom left corner of the post of the person
addrep01.gif

Go to the top right corner of your thread
addsolved01.gif

[Digidre]

Dear Sandy,

I was out of town for 5 days. I will check your posts today.

Greetz
André

[sandy666]

No prob