Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

What I want to achieve:

select list A, B or C from a dropdown, when pressed, list of values displays underneath. I would also like the list to be populated from items in a table (which would have many duplicates, which I wouldn't want included multiple times)


excel help image


I hope this makes sense! in summary, I want a dropdown menu to display different cells from a table - this will be similar to a vlookup, but it will need to display multiple cells (ie. an item name, department code, and cost code)

I'm hoping this is achievable without using VB as I want normal uses to be able to add or remove items if they choose to

share|improve this question
1  
I don't think it's really possible without using VBA, since the dropdown box is a control, but what Mehper said might do the trick. –  Lance Roberts Aug 23 '10 at 16:34
add comment

3 Answers

What you need to do is to use Data Validation.

alt text

Data validation is an Excel feature that you can use to define restrictions on what data can or should be entered in a cell. You can configure data validation to prevent users from entering data that is not valid.

Give an eye to the examples on this official Microsoft page. For thorough details about data validation in Excel 2007, add this page to your bookmarks.

alt text

share|improve this answer
    
Thanks, but it's not data validation that I need - I need the user to select a drop down box and then for a list to be displayed depending on the selection the user makes –  kevyn Aug 23 '10 at 14:35
1  
You can do it via data validation. You just need to use some extra formulas for your criteria. Just give an eye to the examples. –  Mehper C. Palavuzlar Aug 23 '10 at 15:07
1  
+1 Yes, data validation is the correct starting point to get the drop down list. Then use additional formulas to create the list you need. –  Mike Fitzpatrick Aug 24 '10 at 0:31
    
Sorry I'm not sure you're understanding - getting a list into excel is the easy part - what I want to do is to display a list, depending on the option you select from a dropdown box. so you pick 'list 1' from the dropdown, and then the contents of list 1 should be displayed below. if you look at the image in my post - this is an example of what I want to achieve. select 'accounting' and the accounting contents are displayed below –  kevyn Aug 24 '10 at 13:49
add comment

MS Access may be better suited for this task. If you have MS Excel, there is a high chance you also have MS Access.

You are trying to query a database table for the columns named "Item", "Department Code", and "Cost Centre" where "Department" is equal to "accounting".

An MS Access SQL query will look like:

SELECT Item, 'Department Code', 'Cost Centre' FROM my_database_table WHERE Department = 'accounting'

You can wrap up and hide the above SQL statement behind a nice GUI. Adding rows in MS Access is nearly as simple as adding rows to MS Excel (You can optionally add a pretty GUI form for this, too.)

OR... it is also possible to import and export Excel worksheets into an Access Database. I believe you can even link an Excel worksheet to a MS Access database. So you can edit/manage your data in Excel and do your data queries from MS Access.

(I wonder if you could link an Excel worksheet to an Access database, then embed that Access database into the original Excel file...)

share|improve this answer
add comment

If the reason for not using VB is that you want normal users to be able to add or remove items. VB will allow you to dynamically change the dropdown based upon user input and really will give you advantages. A normal user won't really have to know how it works. Unless macro security poses a problem. Everything you want would be relatively easy with a VB approach.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.