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

 

[Dave177]

Skip,
I really am extremely grateful for this. Haven't yet had enough time to implement it but will let you know.

Thanks

David

 

[Dave177]

Hi Skip,

Are you sure the following is correct?

B2: Source: =OFFSET(INDIRECT(ADDRESS(ROW(Fact1)-1,COLUMN(Fact1))),MATCH(SelectedFactory,Fact1,0),1,COUNTIF(Fact1,SelectedFactory),1)

I seem to get an empty drop down...
I thought you would only need OFFSET for this bit?

David

 

[SkipVought]

Do you have Named Ranges of Fact1 and SelectedFactory referring to the correct ranges as defined above?

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Dave177]

Ok, it's not quite as I planned but:
B2 is SelectedFactory
Then there are a few rows of spaces.
In row 6 I have my entry table column titles.
Cell A7 is the first dropdown for getting the Industries available in the SelectedFactory.
So this is where I have:

=OFFSET(INDIRECT(ADDRESS(ROW(Fact1)-1,COLUMN(Fact1))),MATCH(SelectedFactory,Fact1,0),1,COUNTIF(Fact1,SelectedFactory),1)

Interestingly if I select one of the first two factories then some options come up but these are the rows under SelectedFactory, including my table header row that is in column B.
I am pretty certain everything else is exactly how you laid it out above...
Thanks for your continued help!
David

 

[SkipVought]

I just reconstruced a test sheet (took about 3 minutes)

I have a Data > Validation in A7.

Do you have a SelectedFactory VALUE??? You want to have a valid factory selected PRIOR to doing the DV LIST in A7.

Likewise for any other DV Lists that refer to some value.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

BTW, since you change the location of the INDUSTRY DropDown, your second dependency for Workshop will have some changes too. Highlighted are the reference that you will need to change...
[tt]
=OFFSET(
$J$6,
MATCH(SelectedFactory,Factory,0)-1+MATCH($A7,OFFSET($J$6,MATCH(SelectedFactory,Factory,0),1,COUNTIF(Factory,SelectedFactory),1),0),
[red]COLUMN()[/red],
COUNTIF(OFFSET($J$6,MATCH(SelectedFactory,Factory,0),1,COUNTIF(Factory,SelectedFactory),1),$A7),
1)
[/tt]
[red]COLUMN()[/red] changed because the Workshop column must return a 1 and the Qualifications column must return a 2, and it "calculates" this value, based on it's column position which has changed from your initial spec.
$J$6 is where my Master Table is. You must change to where your mast table is, including the SHEET! if on another sheet.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Dave177]

Skip,
Thanks for your help. I'm just getting a bit frustrated because I'm sure I did what you said and I can't work out why it's working for you but not for me.
I did have a Factory selected in SelectedFactory.
I have changed my source for validation in A7 to:

=OFFSET(Fact1,MATCH(SelectedFactory,Fact1,0)-1,1,COUNTIF(Fact1,SelectedFactory),1)

and it seems to work in that I get a choice of Industries specific for any selected factory.
However, it is different to the one you included above:

=OFFSET(INDIRECT(ADDRESS(ROW(Fact1)-1,COLUMN(Fact1))),MATCH(SelectedFactory,Fact1,0),1,COUNTIF(Fact1,SelectedFactory),1)

Forgive my ignorance, but will using the first one present problems in the future regarding my objectives?
And at the risk of being very annoying...(I am really sorry about all of these questions) I tried to paste the following into the source for the data validation for my workshop selection box but it doesn't fit (it won't let me paste it in).

=OFFSET(
$J$6,
MATCH(SelectedFactory,Factory,0)-1+MATCH($A7,OFFSET($J$6,MATCH(SelectedFactory,Factory,0),1,COUNTIF(Factory,SelectedFactory),1),0),
COLUMN(),
COUNTIF(OFFSET($J$6,MATCH(SelectedFactory,Factory,0),1,COUNTIF(Factory,SelectedFactory),1),$A7),
1)

Does it work on yours or should I shorten all of the named ranges etc?

Thanks for all of your help.

David

 

[SkipVought]

Your modification of the Offset formula using Fact1 is just fine! Good for you!

I have run into the length limitation and I don't know the limit.

I'd try drastically shortening the Range Names, starting with SelectedFactory.

You seem to be on the right track and understanding the concept!


Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Dave177]

Great, thank you very much. It works!

 

[SkipVought]

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[GlennUK]

Well done Skip. An interesting and informative thread
====> *



Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

Appreciate that, Glenn. Have an extra pint on me and a great weekend!

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Dave177]

Hi Skip,

Just one thing - how can I limit the options to the resulting drop down lists? I thought this happened automatically but now users also can type free text (which I don't want).
In my Industries dropdown I currently have:

=OFFSET(Fact1,MATCH(SelFact,Fact1,0)-1,1,COUNTIF(Fact1,SelFact),1)

but it allows me to also type anything I want - regardless if it's in the list.

Thanks for any advice.

David

 

[SkipVought]

Not if your dropdown is a Data > Validation -- LIST.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Dave177]

Not sure why it's doing it. I wouldn't be surprised if you didn't believe me and I could provide a screenshot if you wish. In cells A8 and A9 I have used the list properly. However in cells A10:A15 I have typed any old letters (i.e not in the list).

Any ideas?

David