third list dependent on selections of first two 2

[Dave177]

Hi there,
I know there is a lot on validation lists and ways in which you can make the items available in the second dependent on the selection you make in the first.
However, I was hoping that I could use the offset method but have my third list dependent on the selections made in the first and second lists. I am beginning to think that this isn't possible but thought I would try here before giving up.
The idea is to have a workbook with a database query that refreshes every time the workbook is opened. The query imports three columns: Factory, Workshop, Worker.
On another worksheet there would be three validation lists. The selection of a Factory would determine the workshops available in the second list, and a combination of selections in Factory and workshops would determine the workers available in the third list.
I don't think the Indirect method would work as workshops and workers are added and removed from month to month and I don't think that the Indirect method could cope with the flexibility required.
Any ideas?

David

 

[SkipVought]

Hi,

The simplest way is to do a query using your selections as "parameters" in your query.

faq68-5829.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Dave177]

Hi Skip,

I think I tried that but ideally the workbook would be emailed to people for them to enter in data and then send back. So when completing the drop down lists the workbook will not have access to the database...
I appreciate your help though.

David

 

[AnotherHiggins]

See thread68-1469736

There are two links in the first reply and some direct examples in my post dated 30 Apr 08 15:38.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Dave177]

Thanks John, I will give this a look tomorrow.
But a quick question - do you think I can avoid having to define names as in concatenating Make & Type etc (as in the example from the link). Could I just use offset?
Sorry, not too experienced at this but will look closer tomorrow.

David

 

[SkipVought]

I was NOT suggesting that the DATABASE would be accessed.

I WAS suggesting that the WORKBOOK sheets be accesses via a query to yield the expected results. Hence the FAQ.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Dave177]

Skip,
Oops, sorry - didn't think of that!
Thanks, will have a look tomorrow.

David

 

[SkipVought]

The only caveat with mailing workbook or users copying to another location is that the Connection property must be modified via VBA code to "point" to wherever that workbook is saved. It's not difficult, but can be tricky.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Dave177]

Skip,
How about if the data table was held on another worksheet in the same workbook? Is that possible? Would it help?

Thanks
David

 

[SkipVought]

That is normally what I do.

I'ld have a sheet for the three column table.

A sheet for the unique column A values (from a query) that I would use to populate a combobox

I store the combobox selection in a Named Range, something like SelectedName

Use that value to do a query on the master table (sheet) to get the unique list of column B values, used to populate another combobox
I store that combobox selection in a Named Range, something like SelectedCity

Then with the SelectedName and SelectedCity, do a quer on the master list to return whatever to another sheet.

Each Table and query on a separate sheet.



Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Dave177]

Skip,
Thanks for your help!
You say each table and query on a seperate sheet. Does that mean you would not be able to create multiple records/entries using the dependent list boxes on different rows within the same worksheet? Would the query refresh itself for each row?

David

 

[SkipVought]

Please explain the process that you envision, step by step.

Maybe post an example of your source data and relate it to the steps in your process.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Dave177]

What I am hoping for is an excel template that when opened will refresh and bring in all the current data regarding factories, industry and workshops from my database into the worksheet "WorkshopLists". This bit works. For your information a factory can have mutiple industries each of which can have multiple workshops. (Sounds odd but please bear with me)


The template would also have another worksheet that refreshes will all the current data regarding qualifications. I have produced a query that groups qualifications by industry and this bit also works. i.e it refreshes with all the qualifications available by industry in a two column table.

The user who opened the template would then be able to save the template as a normal excel workbook for emailing out to contacts for completion.

Worksheet "Entry" would be where end users would enter the required data. In cell B1 there would be a drop down of a list of available factories. Having selected the factory, users will then be able to complete the table of say 18 rows (B220). In cell B2 there would be a drop down listing those industries available in the Factory selected in B1. Following this selection, in cell C2 there would be a drop down listing those workshops in the factory selected in B1 who are in the industry selected in B2. There would be another drop down in cell D2 listing the qualifications available in the industry selected in cell B2.
The user would be able to make multiple entries of Industry, workshops and qualifications in rows 2:20, each of which should be independent of each other but rely on the factory selected in B1.
Do you reckon this is possible?
David

 

[SkipVought]

users will then be able to complete the table of say 18 rows (B220).

Why 18?

OK, so the SelectedFactory (B1) is used to generate a list of Industries, that will be the source for maybe a Data > Validation - LIST in-cell dropdown. You will ALSO need a list of Industries, Workshops & Qualifications. That probably would be your source table in the workbook, IF it is sorted properly.

Having selected an Industry on any row, now come the trick part for any subsequent list that is based on the Industry row selection. Here, you will need to use the OFFSET function to return the LIST of either Workshops or Qualifications base on the Industry row value.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Dave177]

Hi Skip,
Getting there but when I am trying to set up the connection to my table in my worksheet WorkshopLists, and then enter the criteria in the grid, when returning to excel the whole computer seems to go slow and it takes sometimes 10 minutes for it then to ask "where should the data go" etc. Is this normal?
Also when it asks about the parameters:
"How is the parameter obtained?" - For the factory parameter it should always be cell B1 so for "Get the value from the following cell" I put =Entry!$B$1.
However - for the Industry parameter I want it be B2, B3, B4 etc. depending on which row the user is entering the data. For this reason for "Get the value from the following cell" I put =Entry!$B2. However, it seems that the query always looks to cell B2 for the Industry parameter and not B3 for row 3, B4 for row 4 etc (which is what I would want). Is there a way to change this? Maybe I should just have two constant filters for all the rows?
Thanks for all your help with this.
David

 

[SkipVought]

10 minutes is too long. Depending you the size of your source data table, it might be closer to 10 seconds.

Could you post a sample of your master table so we could see the possible data combinations? Might we have a situation where an industry might exist in more than one factory? Same with Workshops & Qualifacations.

A good example would help.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I'm taking a shot in the dark on the dropdowns for Workshops and Qualifications. This is the Dataq > Validation --LIST formula
[tt]
=OFFSET($H$1,MATCH(SelectedFactory,Factory,0)-1+MATCH($B2,OFFSET($H$1,MATCH(SelectedFactory,Factory,0),1,COUNTIF(Factory,SelectedFactory),1),0),column()-1,COUNTIF(OFFSET($H$1,MATCH(SelectedFactory,Factory,0),1,COUNTIF(Factory,SelectedFactory),1),$B2),1)
[/tt]
where H1 is the anchor reference for the master table (yours might include a Sheet name, like Sheet1!$H$1)) and Factory is the named range for the factory data in your master table.


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Dave177]

Skip,
Thanks a lot for your efforts. I've just got to sort something else first but will hopefully be back on this project in a mo.

David

 

[Dave177]

Hi Skip,
The initial three column list (Factory, Industry, Workshop) which comes from my database is about 400 rows long. Some Factories would have Industries that are also in other Factories but the combination between Factory, Industry and Workshop name would produce a unique Workshop.
eg:
Factory 1 Engineering Workshop 1
Factory 1 Engineering Window Frames
Factory 1 Textiles Workhop 5
Factory 2 Textiles Clothing
Factory 2 Textiles Workshop 1
Factory 2 Engineering Workshop 5
For this reason I don't see how this list could be shortened...

However, the above list is held in worksheet "WorkshopLists" and the idea is that as selections are made in worksheet "Entry", these selections would filter the data in "WorkshopLists" using a separate database query. For the output of this query I have a worksheet "FilteredWorkshops". But when I try to set up this query the computer jams, even though the source data is in the same workbook...

I'm a bit lost about the formula you provided above - would it give a unique list of workshops based on both the Factory and Industry selections?

Thanks for your help! I appreciate your patience!

David

 

[SkipVought]

1. Your Source Data Table as posted with headings
[tt]
Factory Industry Workshop
Factory 1 Engineering Workshop 1
Factory 1 Engineering Window Frames
Factory 1 Textiles Workhop 5
Factory 2 Textiles Clothing
Factory 2 Textiles Workshop 1
Factory 2 Engineering Workshop 5
[/tt]
2. A Factory List table - Query : Select Distinct Factory From [SourceDataSheet$]
[tt]
FactList
Factory 1
Factory 2
[/tt]
3. A Factory-Industry table - Query: Select Distinct Factory, Industry From [SourceDataSheet$]
[tt]
Fact1 Ind1
Factory 1 Engineering
Factory 1 Textiles
Factory 2 Engineering
Factory 2 Textiles
[/tt]
4. Data Validation - LISTS:
[tt]
B1: Named: SelectedFactory Source: FactList
B2: Source: =OFFSET(INDIRECT(ADDRESS(ROW(Fact1)-1,COLUMN(Fact1))),MATCH(SelectedFactory,Fact1,0),1,COUNTIF(Fact1,SelectedFactory),1)
C2: Source: as posted previously HOWEVER, you must change the $H$1 reference to the TOP LEFT CELL of the Source Data Table (including the Sheet; ie Sheet5!$A$1, for instance)
[/tt]
6. All Column ranges uese headings as Named Range.

It works! I used your sample data and got these results
[tt]
Factory 2
Engineering Workhop 5
Textiles Clothing
[/tt]


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}