Multiple Dropdown lists in a sheet with hyperlinks

[DominiX1]

Hello all,

I have approx. 100 company names in the "Summary" sheet and a corresponding spread sheet for every company (These are hidden and are activated via hyperlink attached to the name of the company, i.e. by clicking "Company A" cell in "Summary" it opens "Company A" sheet etc.)

What I am trying to do is with the help of a Dropdown list next to each of the company name in "Summary" sheet to show all of the cities they are providing services in and by clicking on the city it would redirect you to the relevant file (either .pdf .xlsm or .doc) with the relevant documentation

What I had done so far was

Created a list of every city the company is servicing for every company (the list is found in the relevant companies spread sheet, the cities may be similar, the same or entirely different, therefore the list is never exactly the same for all) and hyperlinked it with the relevant document so now by clicking manually on "London" in "Company A" sheet it redirects me to the linked document and by clicking "Oxford" in "Company B" sheet it opens the corresponding file hyperlinked etc.

I want to take it a step further and with Data Validation or Combo Box create a list in the "Summary" sheet against every company name and by clicking that it would redirect you to the linked document, so I would not need to go in to the "Company A" sheet but access it from the "Summary"

So far I had found a solution with a Combo Box and this VBA assigned to the Combo Box

I have selected all of the cities "H2:H20" in "Company A" spreadsheet, defined it's name as "HyperLinks" and selected a cell in the next column "I3" and defined it as "LinkedCell"

In the Format Control I've selected the "LinkedCell" as the Cell Link and "HyperLinks" as the Input Range

Sub DropDown17_Change()
HyperLink_Index = Range("LinkedCell")
      If Range("HyperLinks").Offset(HyperLink_Index - 1, 0).Hyperlinks(1).Name <> "" Then
           Range("HyperLinks").Offset(HyperLink_Index - 1, 0).Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    End If
End Sub

So now it allows me to use the hyperlinked cells from the dropdown menu, BUT!

How would I be able to repeat this for the other 100 companies without creating a macro for every command button or ultimately don't make 100 command buttons?

I have most likely overcomplicated a simple task, therefore I am asking for your advice.

I've tried to be as detailed as possible, I am really hoping for some help on this.

Let me know if anything I've explained is not clear

Many thanks

 

[DominiX1]

Just bumping this, still very much interested in finding a solution.

Many thanks