generate "clickable" link leading to a cell reference?

**magicbob007** · 11-21-2011, 07:37 PM

I know you can copy a cell, then "paste as hyperlink" to generate a link by hand which when clicked will lead you to the pasted cell.

However what I am looking to do is write a formula (no macro) which can generate a link to a cell on a different sheet based upon something like "match" or "vlookup" or "hlookup"

Effectively I need to write a simple search formula that can match a users input and generate a link leading to the cell on anther sheet which matches the given input.

Ex, in cell A1 the user types "301" and in B1 a hyperlink is generated which is "clickable" which upon clicking will lead the user to the cell on the sheet which contains "301". I would like to automate the paste as hyperlink operation based on user input.

Any ideas?

**6StringJazzer** · 11-21-2011, 09:02 PM

Attached is an example using the HYPERLINK function. This assumes data is in a single column. (If your data is not in a single row or a single column then I'm not sure how to solve this.)

In this example if you enter a number that is not found, the result is "Not Found." If the number is found, then the result is a clickable link that says "Go there".

**vlady** · 11-21-2011, 09:38 PM

Hello
Another one.

**magicbob007** · 11-22-2011, 02:07 PM

I like the error checking because the users of my spread sheet will likely input numbers that don't exist and this way they wont be confused when it bounces.

I have a question though...I will be searching single columns, but over multiple sheets. so the same column across say ten different sheets.

All of the data is organized into tables with unique names. One table per sheet, and the headers of each table are identical. So the first column (A) is always the user numbers. I will include a sample that matches my format so you can tell me if you think this is adaptable.

So I know I can refer to each table easily with "Table1[user]" or "Table2[user]" but is there a way I can refer to every "user" column. Could I set up a named range that spans multiple worksheets? Or can we just adapt the formula to work with multiple ranges?

**6StringJazzer** · 11-22-2011, 02:29 PM

Your initial post suggested match and lookup, which can only search one sheet (as far as I have been able to find out). Named ranges cannot span sheets. You need a macro, or nested IF tests, one for each sheet.

**magicbob007** · 11-22-2011, 02:37 PM

Yea I have just been playing with this to test multiple sheets. I could use any functions to accomplish this, just my first guess was to use match. I didn't foresee the complication of multiple sheets. I can pursue the nested If's --a bit hacked together but its only 11 sheets so it will still run fine.