Populate a cell value from a dropdown list

I need to limit the selection of road names.  Thought I would use a dropdown list, but this is quite inadequate.  See simple file where the list of valid roads is in a separate worksheet.  I've also tried using a combo box but can't see how I would link this to a column.  So easy to do in Access!  Hopefully it's just my inexperience with EXCEL...
EEDropdownList.xlsx
kerikeriAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
in C1, you need to put in the Data Validation so that it can refers the value from your list of values.
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
to do that, first go to worksheet: Roads, create a Name.

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

SnapShot2.png
so now you should able to see a dropdown then.

SnapShot3.pngEEDropdownList_b.xlsx
0
Wayne Taylor (webtubbs)Commented:
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
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
0
kerikeriAuthor Commented:
Thank you for your quick responses.  Ryan's was the same solution as what my original description was, not really a solution for me.  Wayne's seemed possible, but I couldn't figure out how the LinkedCell requirement could be defined to a relative cell and as I'm using the logic in a data entry form, that doesn't work.  It has to be able to populate a different linked cell for each row .  So Neeraj's is the one that I'm using.  The only thing not so good is it doesn't include the complete list of roads on initially opening.  However, it's definitely a step in the right direction.  Thanks again all of you.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
0
kerikeriAuthor Commented:
Maybe I shouldn't have closed this so quickly.   Hope you don't mind answering further questions about this solution.
SampleSpreadsheet.xlsm
EEComboBox-Question-.docx
0
Wayne Taylor (webtubbs)Commented:
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

Open in new window

0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
QUESTION:  I don’t understand why you chose to add another column with a heading Roads in the Roads worksheet, and use that as your Named value?
A new list gets populated in column E based on what you type in the combobox e.g. if you type "ara" in the combobox a new list of Roads which have ara in them will be inserted in the column E and that will be used to fill the combobox.

Question:  Where is the combobox?  Is it somehow in the same cell as the data validation?  This is what I need to apply to my real spreadsheet and it’s quite difficult to see where it fits.  Sorry, hope you don’t mind elaborating.
The combobox is hidden and sitting in A1 and it appears only when you select any cell in column A which has a data validation and once you click outside the column A, it disappears again. So there is only one combobox sitting on the sheet and it moves to the cell you select in column A. Also the combobox is linked to the selected cell dynamically by the code.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kerikeriAuthor Commented:
Thank you for your latest comment Neeraj - I need to apply this to the spreadsheet attached in my last comment:  "The combobox is hidden and sitting in A1 and it appears only when you select any cell in column A which has a data validation and once you click outside the column A, it disappears again. So there is only one combobox sitting on the sheet and it moves to the cell you select in column A. Also the combobox is linked to the selected cell dynamically by the code."   I'm trying to replicate this combo box in my spreadsheet so would like to keep this question open until I've managed to do this.  Thanks, it's one part of my quite busy job right now!
0
kerikeriAuthor Commented:
thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.