Hyperlink and numbered drop down list in excel

0
votes

First of all, sorry about my english :)

in Excel (2010) I have a sheet1. There I have 30 test variants (options). Over all options I need to do the drop down list, which contains numbers 1-30 (because I have 30 optionts of test) and need to write code to hyperlink, which sends me to test, which I chose in drop down list.

How it looks:

My test: (just text) (drop down list)-there I should choose one test option (1-30) (hyperlink named "Go")

Then goes 30 test options.

  • Test variant 1

Task 1

jjjfjfjfjf

Task 2

jdddh

  • Test variant 2

Task 1

jjjfjfjfj

Task 2

jdddh

and till...

  • Test variant 30

How I insert drop down list:(data-data validation-choose list-and select data(numbers from 1-30))

I stuggle with hyperlink, because all tutorial shows, how to write hyperlink code if all my tests options would be in apart sheets, but I have all test options in one sheet. To hyperlink I used code: =HYPERLINK("[DATI_8.xlsx]list!"&B3;"Go"), where DATI_8.xlsx is location, list! sheet name where are drop down list, &B3 drop down adress, "Go" just name of hyperlink, but it is not working.

Thank you!

1
exceldrop-down-menuhyperlink

1 Answers

0
votes

Consider something like this (below)

Drop-down is in A1, and cells to jump to are below (but could as well be on a different sheet)

HYPERLINK formula in C1 is as shown.

=HYPERLINK("#Sheet1!A" & MATCH("Option " & A1,A:A,0),"Go")

Uses MATCH() to locate the correct row to jump to.

enter image description here

Credit to: http://www.myonlinetraininghub.com/excel-factor-18-dynamic-hyperlinks-and-the-magic

EDIT: this should work for matching on two columns. Note you need to use Ctrl+Shift+Enter to apply the formula, since it needs to be entered as an array formula.

=HYPERLINK("#Sheet1!A" &MAX(ROW(A:A)*(A:A="Var Nr.")*(D:D=A1),0),"Go")

enter image description here