Hyperlink in Drop down list (not using VBA pls)

[Chanzuihou]

Hi everyone,

At work, I've encountered a task and would greatly appreciate anyone's valuable help.

One of my data sheet is quite a long one, ie from column "A" to column "VV", that means lots of navigating every time when I want to find a specific area (horizontally), which is a bit painful.

The data is structured into a few blocks, ie in my attached test Excel file, they are AAA, BBB, CCC and DDD in the tab "Data".

I 've created a drop down list in cell A1 in "Data" sheet, and also I've created hyperlink, linking to the desirable location in tab "List". I also put hyperlink formula in B1 next to A1, I was hoping every time when I select a value in A1, B1 can update hyperlink, so when I click B1, it takes me to the desirable cell location.

Is this achievable at all? Thanking everyone in advance and very much appreciate it

Best regards


Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	AAA	Go			AAA					BBB				CCC				DDD
2
3
4
5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Data​

Worksheet Formulas Cell Formula B1 =HYPERLINK("[Test.xlsx]Data!"&A1,"Go") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Excel 2012

	A
1	Division
2	AAA
3	BBB
4	CCC
5	DDD
6
7

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>

List​

 

[Kenneth Hobson]

=HYPERLINK(CONCAT("[",MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",
CELL("filename",A1))-FIND("[",CELL("filename",A1))-1),"]Index!", A1, 1),A1)

 

[Chanzuihou]

Thanks Kenneth, I've tried the formula (replaced "filename" with actual file name with extension, ie xlsx, in the end), but somehow it returns "#NAME?". I am not sure where I did it wrong?

One thing to mention is that the drop down list reference area is in another tab (named "List")

In regards to "filename", do I need to enter full file name, together with extension in the end?

Thanks

 

[Kenneth Hobson]

You said match to sheet Index. So, if the reference of AAA is to sheet AAA do you need a cell reference or just to the sheet?

If to a sheet in A1 and start at A1 in that sheet:
=HYPERLINK(CONCAT("[",MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",
CELL("filename",A1))-FIND("[",CELL("filename",A1))-1),"]", A1,"!A1"),A1)

The Concat() string is easy to see how it works. I guess you can hard code the workbook filename with []'s around it but I tried to make it more universal should you change the filename.

Breaking out just the base filename:
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",
CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

 

[Chanzuihou]

Thanks a lot Kenneth.

Apologies I may have not explained clearly in my example. the reference of AAA is to to a cell reference. In the above example, it refers to cell location E1, and BBB refers to cell location J1 and CCC refers to N1 etc.

So longer of the short is,
- A1 is a drop down list. When "AAA" is selected, B2 will update Hyperlink and referencing to cell location E1 (within the same sheet)
- So when I click B1, my mouse will take to cell location E1.

Thank you so much and best regards

 

[Eric W]

One thing to keep in mind is that you can use "#" instead of the file name, if you are referring to the current file. So if your drop down list is a list of actual cell references (E1, J1, N1, R1), then you can simply use:

=HYPERLINK("#"&A1,"Go")

The nice thing is that also works for named ranges. If you name cell E1 "AAA", then the same formula will work with AAA in the dropbox.

If you don't use an actual cell reference, or a named range, I assume you want to search along row 1 for a match and create a hyperlink that way. If so, try:

=HYPERLINK("#"&ADDRESS(1,MATCH(A1,B1:VV1,0)+1),"Go")

 

[Chanzuihou]

Thanking you so much Eric,

I tried your 2nd suggestion and it worked perfectly!!! Greatly appreciate

Best regards

 

[Eric W]

Glad we could help.