Dynamic Drop Down List Variation

**changpion** · 11-30-2010, 02:36 PM

Hi,

I've been scratching my head, seen other threads and trying to work out how to do this, but for the life of me I'm stumped!
The background:
Basically I have a list of well over 1000 people linked to diferent companies in one table, and in another table a list of all the different jobs a person can do for a company which is about 800 long.

In this list of 1000 people and companies it actually works out that there are about 300 people, but they are able to work for different companies. And in doing so can also do different jobs for said company: eg John could work for Company A as a Skill1 and a Skill2 and so needs to have two entries.

The list of 1000 people is every changing and can increase (or decrease, but that's unlikely).

The Problem:
With these two lists i'm entering this into another table. This table uses data validation. First for the Name and ref number of the people and then for the company and skill. What I'm after is depending on the person selected in column one, the drop down will only show the companies and skill that person has a relationship with. I've attached an example to try and show what I'm after. So using that, if John were selected in column 1 then he should only see the company and skills for Company A and Company C, reducing the list in the drop down.

I hope someone understands this, cos I'm baffled with indirects and substitutes!

Thanks in advance...

**alansidman** · 11-30-2010, 02:56 PM

Look at Debra's web site. Here is a link on dependent combo boxes.

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

Alan

**changpion** · 12-01-2010, 05:35 AM

Thanks Alan,

I had a good look at Debra's site before, but I think if it were a simple list of not quite as many records in each list it would be fine, but with the volumes of data we're dealing with I don't know if it's going to work...

I'm thinking Excel may not be quite up to the job as it's something that's built into Access when you enter details into a form.

Thanks for your help.

**Blake 7** · 12-01-2010, 09:49 AM

EDIT: I just realised that the attachment I posted is from Debras site!! sorry.

But, if you look at how it's done then I am sure you will be able to replicate.....

Just remember to sort your data properly........

Hi There, this file should help you to do what you want.

Cheers

**changpion** · 12-02-2010, 02:10 PM

Thanks for your help Blake7, but the problem is to do more with the quantity of data.

using this method, I would need to create a table that had each person with the companies they work for beneath them. But with 350 people the table would be very wide.

I'm thinking this might be too difficult with it needing to be updated weekly.