Hyperlink to cell in sheet based off of list of values

[gaudrco]

Hi,

I am tying to find a way to create a hyperlink that goes to a specific cell in my sheet. I want the hyperlink to be dynamic and change based off a data validation list of values.

For example:

Cell B2 contains my list of values, so in B3 I would like toinput a formula that says when B2=”Apple”, then hyperlink to cell D6, when B2=”Banana”,hyperlink to cell D57 and so on…

Any ideas on how to achieve this?

Thanks

 

[FormR]

when B2=”Apple”, then hyperlink to cell D6, when B2=”Banana”,hyperlink to cell D57 and so on…

Hi, how do we know that "apple" links to cell D6 and "banana" links to cell D57 - what is the logic? Are those values in the cells you want to link to?

 

[gaudrco]

The point of what I am trying to accomplish is a simple way to navigate my spreadsheet.

My spreadsheet has many rows so the logic is if I choose ‘Apples’in the dropdown list of values in cell B2, then cell B3 will dynamically changeto become a hyperlink that will direct me the cell in my spreadsheet that hasto do with Apples, which happens to be D6. Likewise, if I decide to change B2to “Bananas” then B3 will change to the hyperlink that directs me to the cellin my spreadsheet that has to do with Bananas, which happens to be D57.



I tried a formula that looked like this but I got an errorthat said “Can’t open specified file”


This formula is located in cell B3



=IF($B$2="Apple",HYPERLINK(D6),IF($B$2="Banana",'HYPERLINK(D57)))

 

[FormR]

Hi, I understand what you are trying to do - I'm asking if there is a way to logically determine that apple links to cell D6 and bananas links to D57?

If there is no logic, I'd suggest you create a list of the drop down values and the cell you want each to link to (cells E2:F4) in the example below and try like this:

Excel 2013/2016
	B	C	D	E	F
1	Dropdown	Link		Fruit	Link Cell
2	apple	Click here..		Apple	D6
3				Banana	D57
4				Pear	D88
Sheet1

Cell Formulas
Range	Formula
C2	=HYPERLINK("#"&VLOOKUP(B2,$E$2:$F$4,2,0),"Click here..")

 

[FormR]

If you really wanted to hard code the values and cell references in the formula, you could try like this.

=HYPERLINK("#"&CHOOSE(MATCH(B2,{"apple","banana","pear"}),"D6","D57","D88"),"Click here..")

 

[gaudrco]

I tried both methods and they both work! I prefer the hard coded method so that is what I went with. Thank you very much for you help!

 

[FormR]

I prefer the hard coded method so that is what I went with.

Great, although I missed the match_type parameter of the match() function - for robustness you should include the addition in red below:

=HYPERLINK("#"&CHOOSE(MATCH(B2,{"apple","banana","pear"},0),"D6","D57","D88"),"Click here..")