using SMALL, INDEX, with IF conditional adding a HYPERLINK

gabbagab

New Member
Joined
Apr 29, 2019
Messages
2
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:

applesapples are green
applesthey come in different sizes
appleshttps://www.infoaboutapples.com/apple.htm
bananasare yellow
bananasare bent
bananashttps://www.allaboutbanans.com/yellow.htm
pearare squishy
pearcan be yellow
pearhttps://pearsaregood.com/stick.thm

<tbody>
</tbody>

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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

gabbagab

New Member
Joined
Apr 29, 2019
Messages
2
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,099,914
Messages
5,471,478
Members
406,764
Latest member
ExcelMaker007

This Week's Hot Topics

Top