Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

excel and drop down lists

Posted on 2014-12-27
8
Medium Priority
?
193 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Meetings to discuss business process can waste time, and often do .  The meeting's dialog can get confusing when participants have different professional perspectives and backgrounds.  A jointly-developed process picture helps wade through the confu…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

885 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