Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

On one worksheet I have a table of data that includes an item name, person name, price, and a URL.

On an overview worksheet in question I use the FILTER formula to list the items by person using the following formula: =FILTER(Data!A2:A14,Data!B2:B14=D2). I would like to have this cell also have be hyperlink to the item (if there is a URL in the found row).

Is this possible without a script? (With enough scripting work I'm sure I can do whatever I want but that feels like the hard way)

EDIT

I have discovered the HYPERLINK function which seems to be the key but I cannot get it to work.

At first I put the HYPERLINK function on the inside of my filter like this: =FILTER(HYPERLINK(Data!D2:D40, Data!A2:A40),Data!B2:B40=D2)

However this gave this message: error: Mismatched range lengths

Then I thought to combine the URL and text after the filter like so: =HYPERLINK(FILTER(Data!D2:D40,Data!B2:B40=D2),FILTER(Data!A2:A40,Data!B2:B40=D2))

Which worked for the first value in the list but any further cells (that use CONTINUE) are empty.

EDIT 2

I was hesitant to do this at first but here is a link to my spreadsheet. Just don't tell my family what I'm getting them lol. I went ahead and made a copy so don't worry about messing it up. You can see my attempt at it along a manually created example of what I want it to look like.

share|improve this question
    
Do you want the URL to appear on the filtered list? –  Jacob Jan Tuinstra Dec 13 '13 at 21:01
    
No, that would be trivial. I would like the text of the cell to be a clickable link as it would appear if the value of the cell was a URL. –  Fr33dan Dec 14 '13 at 17:21
    
Why don't you share a doc with us, and show us the expected outcome? –  Jacob Jan Tuinstra Dec 14 '13 at 19:08
    
@JacobJanTuinstra I was wary about this at first since the contents are "secret" but I highly doubt any of my family and friends will see this question. –  Fr33dan Dec 16 '13 at 15:39
    
Make sure to buy something for Beth, Wendy and Granny !! –  Jacob Jan Tuinstra Dec 18 '13 at 20:58

1 Answer 1

up vote 0 down vote accepted

You were so close !!!!!!!!

Use the following formula.

Formula

=ARRAYFORMULA(
   HYPERLINK(
     FILTER(Data!D2:D40,Data!B2:B40=D2),
     FILTER(Data!A2:A40,Data!B2:B40=D2)
   )
 )

Explained

The ARRAYFORMULA will take on the ranges, set in the filter, and return the matches.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.