Data Validation / Drop Down Menu from a database

**r.kondapalli** · 01-06-2011, 01:50 PM

Hi All,

Firstly, A Happy New Year & thanks in advance for looking at this post.

I am actually looking to set up a drop down in an Excel sheet from a database (sql server)

Can you explain me in detail how I can do that please.

I have a list of names in the Column "Name" in a table "Staff" & Server "BT6531" which is the database. We are using SQL Server.
I am designing an excel sheet "StaffInfo.xls" in which a cell "K2" should pick up all the Values of "Name" & give them as a drop-down list for a user to select one.

Can you explain me how to do this please.

Ps. This list is dynamic & the table gets updated on regular basis.

**jeffreybrown** · 01-06-2011, 02:21 PM

Hello & Welcome to the Board,

You may want to take a few minutes and read the Forum Rules.

8. Don't cross-post without a link. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post. Expect cross-posts without a link to be closed.

http://www.mrexcel.com/forum/showthread.php?t=519457

**r.kondapalli** · 01-07-2011, 09:44 AM

Sorry, jeoff. It was happened due to lack of knowledge.
How do you think this can be resolved.
Should I close all others? or how can I act?

**jeffreybrown** · 01-07-2011, 10:17 AM

First, I would suggest you stick to this thread or the one from the other forum, but not both.

Second, if you stay here on this forum you can upload a sample workbook with the results you desire which can prove to be very useful.

**r.kondapalli** · 01-07-2011, 10:58 AM

Thanks Jeff...
I woud like to stick to this thread..Pls help

In the attached file...If you consider the column K(other than K1) in sheet1 of StaffInfo.xls, it has got a drop down. But the values are stored in the sheet2. The drop down is based on the values in sheet 2.

We have all these in a database (which is dynamic & updates on regular basis)
Database details:
SQL Server DB (ODBC)
Server Name : PROD561
Database Name : BT6531
Table Name : Staff
Column name : Name

Output of the Table is also attached DBOutput.xls
So, as mentioned, this database keeps updated & the names can either be added or deleted.

CAN I HAVE THESE NAMES AS A DROP DOWN IN MY EXCEL FILE PLZ

Thanks

Ps. When a name is added or deleted in the database, it should reflect in the Excel dropdown list

**jeffreybrown** · 01-07-2011, 01:02 PM

If I understand you correctly, you want to update the name list on StaffInfo.xls Sheet2 whenever the names on DBOutput.xls Sheet1 may change.

Place both StaffInfo.xls and DBOutput.xls in same location.

Alt + C >> Copy macro below
Open StaffInfo.xls
Alt + F11 >> Opens VBE
Alt + I, M >> Inserts new module
Alt + V >> Paste macor in new module
Alt + Q >> Close VBE & return to Excel

Where do I paste the code

Please Login or Register  to view this content.

in the name name manger update "names" with

=Sheet2!$A$1:INDEX(Sheet2!$A:$A,MATCH(REPT("Z",255),Sheet2!$A:$A))

This will make the list dynamic as the list grows or shrinks.