excel and drop down lists

I have an excel spreadsheet with a drop down list (data validation) which is being fed from a sql connection
I want to be able to select a value from this list and get the corresponding row vale in another drop down list showing up on the same sheet

Can someone please give me a scenario to do this

I am familiar with excel and data validation so I only need to know the sequences involved
Who is Participating?

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

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.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Well, it sounds to me that you need a data connection to your SQL server, and the results of a query in a second (hidden) spreadsheet.

This query should have:

1. the value the user wants to look up, and
2. the value you want to return.

The drop-down list then looks at 1., and when the user has selected a value, you use a VLOOKUP to look up 2.

However, you mention a second drop-down box; is there a 1-1 relationship between the two values, or a many to one? If the latter, then because you want a correlated query, you should have a table which returns only the matching records based on the entry of 1., which will give you the data you need for the second drop-down box.
doctorbillTechAuthor Commented:
The sql connections are all set up and the data is on a spreadsheet (spreadsheet 1)

on a second spreadsheet, using spreadsheet 1 as a source:
I need to be able to select column 1 - Row 1 in one drop down list and the corresponding column 2 - Row 2 will be shown up in the second drop down lost and so on down the rows
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
By far the easiest way would be to have it as a PivotTable, with the first drop down list as the Page, and the second list being the Rows.
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
If you do want two correlated drop-down boxes, then you will need to populate the second somehow.

Look at my solution here http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28548461.html for some examples.

The first drop down list is populated by a dynamic range, and you could turn the results into a range source for the second drop down box.

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
doctorbillTechAuthor Commented:
can you explain using the pivot table in a drop down list please
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
The Page is already a drop-down list.

It will populate the rows, and you can use that as the source for your second drop-down List (especially if you hide the rows which contain the PivotTable).

So, have your page field in cell A1, and the rest of PivotTable in rows 3-. Your second drop-down box could be in cell D1, for example, and is populated by the results in rows 3-.

Just one way of doing it.
doctorbillTechAuthor Commented:
I don't understand the pivot table
Can you give me a working example please
(I can substitute my own database values)
doctorbillTechAuthor Commented:
thanks very much for your solutions
the examples worked for me
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
Office Productivity

From novice to tech pro — start learning today.