using a hyperlink in a shortened sort list

[MarkRichmond]

I have used one of the amazing tools from this site to sort only the items from a very long list into a shorter list showing only those that are needed, remember this?


Type Name Criteria Output

Fruit Apple Fruit Apple

Fruit Orange Orange

Veg Broccoli Pear

Veg Spinach <- Cell Contains a Formula, But Displays Blank

Fruit Pear <- Cell Contains a Formula, But Displays Blank

Veg Peas <- Cell Contains a Formula, But Displays Blank


In my real scenario the 2nd column of the list (apple, orange, broccoli, etc) has a hyperlink to an online survey which is specific to the data in the record (so that we can retrace respondents. When the sort works and the specific data that I need is brought out into a new list (the output column above) the cell brings the text across from the cell but doesnt bring the hyperlink with it.


I have explored the 'Hyperlink' function but am getting no joy.


The formula that is doing the hard work is beyond me and copied and manipulated from this example from this site.


{=IF(COUNTIF(A:A,$D$2)<ROWS($E$2:E2),"",INDEX(B:B,SMALL(IF($A$2:$A$10=$D$2,ROW($A$2:$A$10)),ROW(A1))))}


Can anybody help with copying the hyperlink as well as the text from the cell into the sorted list?


Thanks


Mark Richmond

 

[MarkRichmond]

I have found the link to the original tool that I have adapted which might explain better than I have so far - a picture paints a thousand words as they say.


http://chandoo.org/wp/2011/11/18/formula-forensics-003/

 

[MarkRichmond]

Dont worry, all resolved. It was down to where I was placing the Hyperlink function. A quick check through evaluating the formula and I realised I was placing it in the wrong place.


Thanks anyway.