Excel - Creating Multiple Lists with a Hyperlink

Question

I have three lists - Devices, Device1, Device2.

Devices - Device1 and Device2
Device1 - 1/1, 1/2, 1/3, 1/4, 1/5
Device2 - 2/1, 2/2, 2/3, 2/4, 2/5

I have the dropdowns working using data validation. When the device is selected in the first dropdown, the second dropdown gives you the correct list of numbers. Beside the second dropdown I want a hyperlink that takes you to a separate sheet for the selected device and the cell for the selected number in the list.

Example:

Dropdown 1 - Device2
Dropdown 2 - 2/4
Hyperlink - should take you to sheet "Device2" and the cell with "2/4" in it.

Can this be done in Excel?

yes it can be done...my question is the cell with 2/4 in it, the only cell with it in it and is it anywhere on the sheet or is it in a specific row/column range? You will want to look at the INDIRECT formula — Forward Ed
– Forward Ed, Commented Oct 23, 2017 at 18:04
The 2/4 (part of the Device2 list) is in a cell beside the first dropdown (Device2). All three lists are on a separate sheet called Lists. I am putting the Data Verification lists on a sheet called Formulas. There are two other sheets (one for each device - Device 1 and Device 2). I used the INDIRECT formula to get the dropdown for the list with the 2/4 in it to work. What I need now is for the third cell with the hyperlink in it to link to the proper device's sheet and cell. The above example should link to sheet "Device 2" / cell "A4". — user2493464
– user2493464, Commented Oct 23, 2017 at 20:00
so based on your comment is it safe to say that */1 is A1, */2 is A2, */3 is A4 and */4 is A4 of the appropriate sheet? — Forward Ed
– Forward Ed, Commented Oct 23, 2017 at 23:51

Forward Ed · Accepted Answer · 2017-10-24 16:39:30Z

1

So thanks in no small part to this stack Q&A I discovered a key missing ingredient. The workbook reference. It also turns out that indirect is not needed so apologies for leading you astray in the comment section.

Now assuming dropdown 1 is in A1 and dropdown 2 is in B2, and all the #/1, #/2, #/3 and #/4 are in column A on their respective sheets and that the last digit represents row number, you could use the following:

=HYPERLINK("#'"&A1&"'!A"&RIGHT(B1))

or

=HYPERLINK("#'"&A1&"'!A"&RIGHT(B1),"Follow me")

now if you don't know what row the #/1 to #/4 are in, but you know what column they are in you could modify the cell address to:

=HYPERLINK("#'"&A1&"'!A"&MATCH(B2,INDIRECT("'"&A1&"'!A:A",1),0))

What had me tripped up is you need to include the work book name in the hyperlink and that # will represent the workbook you are in. It the hyperlink does not work for you, make sure you save the workbook at least once. If the hyperlink does not work for you after changing sheet names, filename, or filepath, make sure your right click on your hyperlink cell and select remove hyperlink. That last step will remove a hyperlink that is created in addition to your formula that seems not to update when things change. Your formula hyperlink will work regardless as it is part of the formula and not the cell property so to speak.

edited Oct 24, 2017 at 16:39

answered Oct 24, 2017 at 0:16

Forward Ed

9,9843 gold badges29 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user2493464 Over a year ago

Thank you so much for your help! Your first solution worked perfectly. The only things I needed to add were the [ ] around the file name (#).

user2493464 Over a year ago

I do have a question regarding your third option. If I use it and replace the # with the [file name] I get #REF! in the cell. Am I missing something? What is the ...&MATCH(B2,INDIRECT(" ' "&A1&"!A:A",1),0))? Is there suppose to be inserted where the " ' " is?

user2493464 Over a year ago

I ran into a slight problem with the first and second option...they take me to the correct sheet, but takes me to the wrong cell. The link is one off (1/1 takes me to cell 1/2). If that makes any sense.

Forward Ed Over a year ago

there was a missing ' in front of the !. I have edited the formula

Forward Ed Over a year ago

For options 1 and 2, is 2/4 or 1/4 in row 4? if they are not options 1 and 2 will not work for you. Options 1 and 2 assume that the number after the / is the same as the row number

|

Collectives™ on Stack Overflow

Excel - Creating Multiple Lists with a Hyperlink

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related