Excel Help needed

Origin

Origin

Origin

Associate
Joined
30 Dec 2003
Posts
1,368
Location
BC, Canada
Hello all, I am wondering if it is possible to use a drop down selection list to determine that certain data is shown. I can create the original list, that bit is okay, it is what it does once that selection is made.
EG. Drop down list contains the following:
Ford
Vauxhall
Nissan
etc.

So if a user selects Ford from that list, then in the cells next to it the following appears
Mondeo 2.0L
Escort 1.6
Focus ST
etc
Is this possible or should I be doing it some other way? It doesn't matter if I have to use another program to do, I just thought Excel would be able to do it.

Any help is appreciated.
Cheers
 
You're probably right. What I am actually trying to do, is create a list of Manufacturers that we sell at work and then this would filter down to the Product Manager and also the product specialist aswell.
This would be used by our sales people to find out who they should speak to about particular products. I thought an excel sheet as everyone in the building has excel, but I'm not sure they all have acess unless I could create the DB and then make it a standalone thing that they can just locate on our intranet.

P.S I really did try googling this but got nowhere.
Thanks
 
If it's just to do that (and it's not going to be changing too much) then Excel should be fine for such a job. The method above should work fine.
 
The data shouldn't change that often, just as and when people leave or move. but that doesn't happen that often at our place.
Thanks again.
 
Dolph said:
Yep, it's possible.

http://www.contextures.com/xlDataVal02.html

However, depending on what you're actually doing, there may be a better way to do it using a database system such as Access
Click to expand...
Thanks! :) Have wanted to know how to do this for a while now but forgot to ask or had no example to try it with.

I'm going to try and work out the Access method of this for a database I could do with at work. :)
 
MarcLister said:
Thanks! :) Have wanted to know how to do this for a while now but forgot to ask or had no example to try it with.

I'm going to try and work out the Access method of this for a database I could do with at work. :)
Click to expand...

The access method is easy, forms with combo or list boxes populated by queries based on the selection in the previous box, with the second combo box requeried as an afterupdate event from the first :)
 
Last edited:
Ah, thanks Dolph. Always something I wanted to be able to do. When I worked as a teaching assistant in a local school I was always in awe of the technicians' IT database. They'd open it up, select a PC at the school and all the serial numbers for that PC would appear in the boxes.

Something I never learned to do in A-level IT. I will look for some sites to give me some tutorials on this.
 
If you can't find anything give me a yell and I'll try and knock something together if you want.
 
Hello again Dolph. Could I take you up on your offer? :) I've googled for Access 2007 + dependent lists but I don't really get much and Excel stuff comes up a fair bit. :(
 
No worries. I've put together a very quick demonstration database if you want me to email it to you, but the guide is as follows.

This method assumes you've got the different combo box/list box contents in different tables (which is the only way to do it really)

1. Create your tables and define relationships. For example, I created

Group
Group ID (primary key)
Group name

Sub group
Sub group ID (primary key)
Sub group name
Group name (foreign key from above, stored as the group ID)

2. Create a query based on the table for the dependant list (in this case sub group), save it, we'll edit it later. (I called this subgroup population)

3. Create a form containing two combo boxes. Set the source for the first one to the table group, and the source for the second to the query you created in 2.

4. Open the query you created in 2, then set the criteria on group to the first combo box on the form (use the build option and pick it from loaded forms>form name).

5. Go back to the form, and select the first combo box. Open the properties box and choose the event tab, then find onchange and click build, then select code builder. This will open a VB window.

6. Enter the following in the onchange event for the combo box.
Code: 
combo2.requery
(Insert the name of the second combo box as defined on your form)

This piece of code causes the second combo box to re-request the data, which as it comes from a query where the selection criteria is the value in the first combo box, ensures you get a dependant list.

Switch the form to form view and marvel at the dependant list :)
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom