Drop down list with hyperlinks

[munchems]

Hello,

I have a drop down list of two items that I would like to link to two seperate sheets. Is there a way to do this when the drop down list items are different from the sheet names?

Like in this video but with different sheet names:

TIA!!

 

[Logit]

Yes.
The formula is referring to an name that is located in cell B1 and that name refers to a tab name located below. So ... just make sure your drop down list
matches the sheet names located on the tab and in the same order as they appear (left to right) on the tabs.

 

[munchems]

Yes.
The formula is referring to an name that is located in cell B1 and that name refers to a tab name located below. So ... just make sure your drop down list
matches the sheet names located on the tab and in the same order as they appear (left to right) on the tabs.

Yes, I understand that for that specfic formula to work that the drop down items must match the sheet names, however in my situation the drop down items can't match the sheet names so I am looking for an alternative formula for this to happen. Thanks!

 

[Jaafar Tribak]

If you want to have different Alias names for the sheets in the dropdown such as:

TOC worksheet

Where the Alias names are added in the Lists worksheet in Column A and the corresponding actual sheet names in Column B as in the image below. (Change Alias names to suit)

Next, add a helper cell (D1) that contains the formula: =INDEX(B1:B4,MATCH(TOC!B1,A1:A4,0),1) where TOC is the name of the dashboard sheet that has the dropdown

Lists worksheet

and then edit the formula of the my_sheet Name to reference the above D1 cell in the Lists worksheet as follows :


=INDIRECT(ADDRESS(1,1,,,INDIRECT("Lists!D1")))

 

[munchems]

If you want to have different Alias names for the sheets in the dropdown such as:

TOC worksheet
View attachment 107994


Where the Alias names are added in the Lists worksheet in Column A and the corresponding actual sheet names in Column B as in the image below. (Change Alias names to suit)

Next, add a helper cell (D1) that contains the formula: =INDEX(B1:B4,MATCH(TOC!B1,A1:A4,0),1) where TOC is the name of the dashboard sheet that has the dropdown

Lists worksheet
View attachment 107995


and then edit the formula of the my_sheet Name to reference the above D1 cell in the Lists worksheet as follows :


=INDIRECT(ADDRESS(1,1,,,INDIRECT("Lists!D1")))

Thank you, this worked perfectly and helped me to understand this function even more!