Results 1 to 2 of 2

Thread: using SMALL, INDEX, with IF conditional adding a HYPERLINK
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default using SMALL, INDEX, with IF conditional adding a HYPERLINK

    I'm wondering if anyone can help me with working out how to add a hyperlink based on certain conditions.

    I have two worksheets - Worksheet "Catalogue" has a dropdown list to select which fruit and another worksheet which has information about the piece of fruit selected. There are multiple lines about each of the pieces of fruit. The second worksheet Fruit looks like below:

    apples apples are green
    apples they come in different sizes
    apples https://www.infoaboutapples.com/apple.htm
    bananas are yellow
    bananas are bent
    bananas https://www.allaboutbanans.com/yellow.htm
    pear are squishy
    pear can be yellow
    pear https://pearsaregood.com/stick.thm

    I have found when I parse this information through INDEX - the links in the doco get removed. This is my formula located on the Catalogue worksheet:
    =IFERROR(INDEX(Fruit!$D$1:$D$1179,SMALL(IF(Catalogue!$D$7=Fruit!$B$1:$B$1179,ROW(Fruit!$D$1:$D$1179),""),ROW()-8)),"")

    Using this formula, I get the correct result but the hyperlinks are no longer hyperlinks. To fix this I created an extra column in the fruit sheet to identify which lines are hyperlinks and modified the formula to be:
    =IFERROR(
    IF(VLOOKUP(INDEX(Fruit!$C$1:$C$1066,
    SMALL(
    IF(Catalogue!$D$8=Fruit!$B$1:$B$1066,
    ROW(Fruit!$C$1:$C$1066),
    ""),
    ROW()-10)),Fruit!$C$1:$D$3595,2,FALSE)="hyperlink",
    "gee whiz - stupid hyperlinks",


    INDEX(Fruit!$C$1:$C$1066,
    SMALL(
    IF(Catalogue!$D$8=Fruit!$B$1:$B$1066,
    ROW(Fruit!$C$1:$C$1066),
    ""),
    ROW()-10))),
    "")

    I was testing above - In the line where it says "gee whiz - stupid hyperlinks", I did have hyperlink("www.google.com") - just to see if I could get a hyperlink to work. This did work however if I copy the formula to all the lines being outputted, all of them end up being hyperlinks. If I remove the syntax of hyperlink("www.google.com"), then I see "gee whiz - stupid hyperlinks" in the correct spot and none of the other output for the selected fruit is a hyperlink.

    This sounds all very convoluted - so if anyone is able to help me figure out how to detect within the array that there is a hyperlink and apply the hyperlink command I would really appreciate it. Or if there is a way for me to parse the information from the fruit workshop and keep the hyperlink intact that would be great too.

    Thanks a lot!
    Gabby

  2. #2
    New Member
    Join Date
    Apr 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: using SMALL, INDEX, with IF conditional adding a HYPERLINK

    so I think the problem for me is as basic as - using hyperlink() isn't behaving as expected.

    eg. using the following data
    horse
    dog
    http://www.animals.com
    cow

    If I copy the below formula in the column next to the above data to create a hyperlink - all of the data in column 2 becomes a hyperlink regardless of where it has http in the name or not.
    =if(left("a1,4)="http",HYPERLINK(A1),"No link")

    The output looks like this:
    horse no link
    dog no link
    http://www.animals.com http://www.animals.com
    cow no link

    Is this normal behaviour? all of the "no link" cells are also hyperlinks and receive an error of "the address of this site is not valid ..etc" which is true as they aren't valid links. Why would the formula apply a hyperlink to it despite it not meeting the criteria?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •