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
doctorbillTechAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Phillip BurtonConnect With a Mentor Director, 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.
0
 
Phillip BurtonConnect With a Mentor Director, 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.
0
 
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
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Phillip BurtonConnect With a Mentor Director, 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.
0
 
doctorbillTechAuthor Commented:
can you explain using the pivot table in a drop down list please
0
 
Phillip BurtonConnect With a Mentor Director, 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.
0
 
doctorbillTechAuthor Commented:
I don't understand the pivot table
Can you give me a working example please
(I can substitute my own database values)
0
 
doctorbillTechAuthor Commented:
thanks very much for your solutions
the examples worked for me
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.