Excel - Filter Help 2

[arvarr]

Hi
Is there anyway to restrict the filter to selection made?

Example:
If Company 10 selected on the selection worksheet, then only those Departments associated with Company 10 is listed for selection.
If Company 10 selected on the selection worksheet, then only those Sub Departments associated with Company 10 is listed for selection.

Thanks,
arv

 

[Andrzejek]

Isn't that how Filter works in Excel anyway?

If you filter on Sub Department, you will get all entries from Sub Department column, but if you first Filter on Company column, select 10, and then Filter on Sub Department column, you get only Sub Departments for Company 10

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[arvarr]

Hi Andrzejek
Sorry, i might not have stated my question in the right sense.
The "Selection" worksheet acts more like a List of Values.

Refer to "Department" worksheet where there're multiple Company combinations.
So, if Company 10 is selected on the "Selection" worksheet, the objective is to only see the List of Values associated with Company 10 from the "Department" worksheet.
Deparments associated with other company numbers shouldnt be seen in the List of Values in the "Selection" worksheet when Company 10 is selected.

Thanks,
Arv

 

[Andrzejek]

Is that what you are trying to do: How to Create a Dependent Drop Down List in Excel

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[arvarr]

Hi Andrzejek
Yes, sort of the logic that i need.
I have tried to follow the steps to no avail.
Thanks,
arv

 

[SkipVought]

Big RED flag:

The Company worksheet and the Department worksheet.

You better have a Table/worksheet that has at least Company,Department and Sub Department, in what I'll call the Master Table.

Presumably, or at least in theory, Companies can be added to the Master table. So your Companies List query needs to run BEFORE your Companies Drop Down is accessed.

Once that selection is made, the SelectedCompany is used as a parameter to run a query of the Master Table to generate a Departments list for the Departments Drop Down.

Then after that selection is made, the SelectedCompany & SelectedDepartment is used as a parameter to run a query of the Master Table to generate a SubDepartments list for the SubDepartments Drop Down.

Your three individual queries can each be on separate sheets or all on one sheet, but not on the Master Table sheet.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[arvarr]

Hi Skip
The Company,Department and Sub Department has to be kept in separate worksheets as they are imported but can convert to tables.

Do you mind giving me an example on the sample file? I don't think I am following you re the query.

Thanks,
Arv

 

[SkipVought]

The Company,Department and Sub Department has to be kept in separate worksheets as they are imported but can convert to tables.

That's a curve. I was always dealing with changing shop floor data and dealing with corporate database tables.

But, Yes, to do it right, it should all be in one table, as per this example: Company, Dept & Sub, sorted in that order and then a Helper column concatenating Company & Dept.
[pre]
Company Dept Sub Helper
10 00 000001 1000
10 00 000004 1000
10 00 000060 1000
10 01 000002 1001
10 01 000010 1001
10 01 000070 1001
10 02 000003 1002
10 02 000050 1002
10 02 000080 1002
20 05 000085 2005
20 05 000063 2005
20 05 000022 2005
20 06 000001 2006
20 06 000058 2006
20 06 000041 2006
20 07 000006 2007
20 07 000079 2007
20 07 000093 2007
[/pre]

Make the table a Structured Table. My table name is Table1.

Here's the source for the Company Data Validation List, Department Data Validation List & Sub Dept list
[tt]=UNIQUE(Table1[Company])
=UNIQUE(OFFSET(Table1[Company],MATCH($B$3,Table1[Company],0)-1,1,COUNTIF(Table1[Company],$B$3),1))
=UNIQUE(OFFSET(Table1[Company],MATCH($B$3&$C$3,Table1[Helper],0)-1,2,COUNTIF(Table1[Helper],$B$3&$C$3),1))
[/tt]

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[arvarr]

Yikes, that leads to another pain point.

In reality, I will have 10 different worksheets/tables.
1. Not sure what/how is the best method to get all possible combinations across the 10 worksheets/tables to combine into one
2. Should the helper be the same combination?

Thanks,
arv

 

[SkipVought]

Devil's in the Details.

Provide the details.

Are you claiming that you have more than 3 level of hierarchy up to 10?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[arvarr]

Hi Skip
New file uploaded.

Structure 1 and Structure 2 worksheet - Dependent on Company selected
Note that the full set of data is approx. 100,000 line for Structure 1 and Structure 2

Site 1 & Site 2 worksheet - These are generic values and full list should be in the List of Values irrespective of company selected

Thanks,
arv

 

[SkipVought]

Maybe I'm missing something.

Lets go back to Company, Department and Sub Department. I assumed from this...

[pre]
Company Department Sub Department
[/pre]

...that a Company selection resulted in a Department List for the Selected Company and a subsequent Department selection resulted in a Sub Department list for BOTH the Selected Company AND the Selected Department.

If that is true, then your data does not support this result and maybe that was simply my assumption.

So please explicitly explain exactly what you expect to happen for a specific value of Company 20 in each subsequent drop down in your most recent upload, because this isn't looking like anything I've encountered.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[arvarr]

Hi Skip
If Company 20 is selected, the expected behavior is to be able to see and select the List of Values as per below:

Department - B13:B31 (List of Values from Department worksheet)
Sub Department - B14:B25 (List of Values from Sub Department worksheet)
Structure 1 - B37:B71 (List of Values from Structure 1 worksheet)
Structure 2 - B37:B71 (List of Values from Structure 2 worksheet)
Site 1 - B2:B101 (List of Values from Site 1 worksheet)
Site 2 - B2:B101 (List of Values from Site 2 worksheet)

Thanks,
arv

 

[SkipVought]

You have a data problem with the Department data in your 2 Structures tables.

In Department your value is 2 Digits.
In the Structure tables your value is 3 Digits.

I have the displayed above the entry rows.

I'm not sure how you want the values displayed or as a list in a Data Validation List.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[arvarr]

Oops, my bad. It should be 2 digits.

 

[SkipVought]

There's your answer. Just fix your data.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[arvarr]

Awesome, that worked Skip.
From the list generated via the formulas, I am trying to get it into a Data Validation List.
However, I am not sure what options/formulas can cater to the ranges.
I.e. depending on the selection, the range of selections may be longer and shorter.
I was tempted to have a bigger range in the Data Validation List but then, it also reflects a lot of blank lines in the list.

Thanks,
arv

 

[arvarr]

Also, Thanks for your help Andrzejek

 

[Andrzejek]

So, what is the point of Selection worksheet?
You can select Department, Sub Department, etc. and then what...?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[arvarr]

Firstly, the aim is to try and eliminate opening up those sub department, structure 1 and structure 2 List of Values for selection.
Secondly, reporting is also associated with correct values selected.
Having incorrect combinations will skew results.

Thanks,
arv