Populate a cell value from a dropdown list

in C1, you need to put in the Data Validation so that it can refers the value from your list of values.

to do that, first go to worksheet: Roads, create a Name.


now go back to worksheet: MainSheet, select cell C1, then go to tab: Data > Data Validation and add this.


so now you should able to see a dropdown then.

EEDropdownList_b.xlsx

In this instance, you might be better off with a Controls Combobox, not a Forms Combobox which you are currently using. Doing so allows the following pertinent properties...

ListFillRange - The range of cells to populate the combobox. Can be multiple columns wide.
BoundColumn - The column from ListFillRange which  returns the selected value. The value isn't necessarily the displayed text.
LinkedCell - The cell which displays the selected value of the combobox.

See the attached example. To view the properties, click Design Mode on the Developers tab, right-click the control and select Properties.
EEDropdownList.xlsx

I think you need a searchable drop down list which gets populated with matching roads as you type.
Please refer to the attached and see if this is what you were trying to achieve.
SearchableCombobox.xlsm

I does show the complete list when activated after selecting a cell in column A.
Watch this short video.
https://www.screencast.com/t/nGbNbccB12Y

but I couldn't figure out how the LinkedCell requirement could be defined to a relative cell

If added manually, you need to set the LinkedCell property of each combobox individually. Alternatively, you can use the below macro to add the combobox to the current selection of cells. It sets the LinkedCell to the cell to the immediate right of the cell the combobox is in.

Sub AddComboboxes()
        
    Dim cell As Range, cb As OLEObject
    For Each cell In Selection
        Set cb = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=cell.Left, Top:=cell.Top, Width:=cell.Width, Height:=cell.Height)
        With cb
            .LinkedCell = cell.Offset(0, 1).Address
            .Object.BoundColumn = 2
            .ListFillRange = "Roads!A2:B786"
        End With
    Next
    
End Sub

Select all Open in new window