Solved

excel and drop down lists

Posted on 2014-12-27
8
187 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 500 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 500 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 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 500 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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

759 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