• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Include a hyperlink within a formula

P

pmaynard

New Member
I have an excel spreadsheet. I have my first tab labeled January. I have a tab for every month. February is linked to January, March to February etc with the following formula =IF(January!A3="","",January!A3). I have a hyperlink (to a picture) in Sheet JanuaryA3 and would like that hyperlink to follow through to the other months and not having any luck with this.


Is this possible to accomplish? If so, please help as I am not able to figure it out.


I hope you can understand what I am trying to do. If not, please let me know. Thanks,


Pam
 
Also, I did take off the last "" as the text in JanuaryA3 did not come across and still received error.
 
Hi Pam ,


I'll tell you what I did , and you can see if it is what you want.


1. In cell B3 on the Sheet2 tab , I created a hyperlink to a picture on my hard disk , whose path is :


E:Documents and SettingsK.NARAYAN RAOMy DocumentsMy Picturesspanish-sky.jpg


This was done by using the INSERT HYPERLINK facility in Excel.


2. In cell B3 on the Sheet3 tab , I put in the following formula :


=IF(Sheet2!B3="","",HYPERLINK(Sheet2!B3,""))


Now , if I click on the cell B3 in the Sheet2 tab , I am taken to the picture ; when I click on the cell B3 in the Sheet3 tab , I am taken to the picture.


Narayan
 
That's exactly what I want to do, however for some reason it is not working for me. Could it be because I have text in cell A3 where I also want to include the hyperlink?


In cell A3 on sheet "January" tab (text includes a product number "KLP001") I created a hyperlink to a picture on my computer with the path of C:UsersOwnerPicturesLexiPLAY.jpg using the same INSERT HYPERLINK. There is text in the


In cell A3 on sheet "February" tab I inserted the formula you sent me. However, since I do want the text from "January" A3 to also carry over to "February" A3 I did alter the formula to carry that text over.


=IF(January!A3="","",HYPERLINK(January!A3, January!A3))


So rather than having HYPERLINK (January!A3, "")) I changed the "" to January!A3.


Either way, if I leave the formula the way you advised, or change to January!A3, I still get the error.


I really appreciate your help on this. You have gotten me further than I was able to get on my own.


Pam
 
I did try it on a blank spreadsheet and was able to get the formula to work as long as there wasn't any text in the cell. Looks like that is my issue at this point. However, I really need to have that product number in there. If you have any ideas I greatly appreciate it.


Thank you for everything,

Pam
 
I GOT IT!!!!!! Yeah!! I added a column in sheet "January" for the hyperlink to store in and added the hyperlink in A3 and also to the text in B3 (so 2 of the same hyperlink in sheet "January".) This is the formula I am using now.


=IF(January!B3="","",HYPERLINK(January!A3, January!B3))


Thank you so much for your time and assisting me with this.


Pam
 
Oh! CRAP! January to February worked but now I can't get March to work.


I'm trying to use the following formula in my "March" tab


=IF(February!A3="","",HYPERLINK(January!A3,February!A3))


Nothing happens the cell just leaves the text of the formula in it and gives me the "This cell is inconsistent with the column formula."


I'm not quite sure why February worked but now March won't. Very confused.


Pam
 
Hi Pam ,


Good that you could resolve your problem ; I saw that everything works as long as there is no text in the cell January!A3 which has the original hyperlink to the picture.


I think there is no solution to this problem. You have used a workaround which involves the use of a helper cell ; there is another workaround , which uses VBA , courtesy this link :


http://www.contextures.com/excelhyperlinkfunction.html


What is to be done is to have the following function in a VBA module :

[pre]
Code: 
Function HLink(rng As Range) As String
'extract URL from hyperlink - posted by Rick Rothstein
If rng(1).Hyperlinks.Count Then
HLink = rng.Hyperlinks(1).Address
End If
End Function
[/pre]
Now , in the second sheet , instead of the original formula :


=IF(January!A3="","",HYPERLINK(January!A3, January!A3))


use the following formula :


=IF(January!A3="","",HYPERLINK(HLINK(January!A3), January!A3))


Now , the second sheet will not only display the text from January!A3 , but clicking on this cell will execute the action of displaying the picture.


Narayan
 
You must log in or register to reply here.
Back
Top