• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Populate a Combo box using external data

You can do this using External data connections in excel.

I am giving an example based on a text-file with combo options, but you can do the same for access or other DBs.

(1)Make a text file with the options you want to see in combo, save it somewhere, say C:/dropdownoptions.txt

(2) Now, go to excel, create a new connection from data ribbon

(3) Select txt file and connect to the file by pointing to the path (this is not so simple, but if you have ever used excel's external data connections feature, this should be simple.

(4) Now, import the data by establishing this connection.

(5) make sure you uncheck "prompt for file name everytime" option.

(6) at this point, excel should create a named range (something like dropdownoptions) for you that points to the imported data.

(7) use this named range as the source for your form-control.

(8) whenever you refresh the data connection, the named range will be redefined automatically by excel. Thus, the combo gets new values as text file is updated.

Let me know if you have some doubts...
Thanks for your first advice!! I did it, and It worked.

But, my next question is <How can I "hide" the source or external data?>

Actually I would prefer to populate a combo-box "directly" from external source without loading the items into temporary cells. Is it possible to do?

You could hide the sheet with the data on it and have it auto-update evertime you opened the spreadsheet