Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

excel and drop down lists

Posted on 2014-12-27
8
Medium Priority
?
195 Views
Last Modified: 2014-12-28
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
0
Comment
Question by:doctorbill
  • 4
  • 4
8 Comments
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 2000 total points
ID: 40520276
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
 

Author Comment

by:doctorbill
ID: 40520323
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
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 2000 total points
ID: 40520367
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 24

Accepted Solution

by:
Phillip Burton earned 2000 total points
ID: 40520370
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
 

Author Comment

by:doctorbill
ID: 40520381
can you explain using the pivot table in a drop down list please
0
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 2000 total points
ID: 40520384
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
 

Author Comment

by:doctorbill
ID: 40520483
I don't understand the pivot table
Can you give me a working example please
(I can substitute my own database values)
0
 

Author Closing Comment

by:doctorbill
ID: 40521404
thanks very much for your solutions
the examples worked for me
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently resolved a client's Office 2013 installation problem and wanted to offer an observation that may help you with troubleshooting similar issues. The client ordered three Dell Optiplex system units with the Windows 7 downgrade option inst…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question