How to use excel to disply a list of cells, depending on dropdown box selection

Question

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)

---

---

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

Answer 1

What you need to do is to use Data Validation.

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.

Answer 2

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...)

Answer 3

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.