×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel - Filter Help
2

Excel - Filter Help

Excel - Filter Help

(OP)
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

RE: Excel - Filter Help

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

RE: Excel - Filter Help

(OP)
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

RE: Excel - Filter Help

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

RE: Excel - Filter Help

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Excel - Filter Help

(OP)
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

RE: Excel - Filter Help

Quote:

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.
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
 

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
=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))

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Excel - Filter Help

(OP)
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

RE: Excel - Filter Help

Devil's in the Details.

Provide the details.

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Excel - Filter Help

(OP)
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

RE: Excel - Filter Help

Maybe I'm missing something.

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

Company	Department	Sub Department
 

...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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Excel - Filter Help

(OP)
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

RE: Excel - Filter Help

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Excel - Filter Help

(OP)
Oops, my bad. It should be 2 digits.

RE: Excel - Filter Help

There's your answer. Just fix your data.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Excel - Filter Help

(OP)
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

RE: Excel - Filter Help

(OP)
Also, Thanks for your help Andrzejek

RE: Excel - Filter Help

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

RE: Excel - Filter Help

(OP)
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


RE: Excel - Filter Help

Quote (arv)

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.

Are we talking about the same things?

Each Formula I posted in each column is what you put into your Data/Validation/List Source box. Each Formula has a Selection Value reference and hence returns the correct list of values, provided YOU make each table a Structured Table and supply that Table Name in each formula.

I forgot to include the formula for the Company DVL Box...

=UNIQUE(Table1[Company])

That's all that goes into the Data/Validation/List Source box when you set up the In-Cell Drop Down. Same for every other box, using the formula provided in that column, provided your tables are ALL converted to Structured Tables and you use the correct Table Name.

I left that for you to do. When you use Structured Tables, you NEVER have to worry about blank lines in your list. Based on your selection of Company or Department, your list will be exactly as long as you have rows in your respective table for each selection.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Excel - Filter Help

Unfortunately, the UNIQUE() function is only available in Excel365.

This is one big reason why I rarely use spreadsheet functions to return arrays but rather use queries to return sets of data that users can see and work with, which is what Excel is all about--users working up close and personal with their data.

Since you never told us the ultimate goal of this workbook, I can't venture a cogent guess of a better method because I have no method to compare other than what you've advanced in vagueness.

What happens in vagueness, stays in vagueness.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Excel - Filter Help

(OP)
Hi Skip
Much appreciated. Tried copying the formula into the Data Validation/List but came up with an error.

Thanks,
arv

RE: Excel - Filter Help

Oh, yes. Now I remember.

You can't use Structured Table references in Data/Validation and other legacy Wizards.

So you have to open the Name Manager and Add Names using the Structured Table reference.

It will take me a bit to get that done for one DVL and I'll let you do the remainder.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Excel - Filter Help

(OP)
What are the likelihood of being able to conduct a search via the DVL?
Thanks.

RE: Excel - Filter Help

Please explain how you want to search.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Excel - Filter Help

(OP)
Hi Skip
Similar to autofilter.

Thanks,
Arv

RE: Excel - Filter Help

Please relate this to your workbook.

You select a Company in the DVL. Now what?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Excel - Filter Help

I've already asked this question, and the response was:

Quote (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.

---- Andy

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

RE: Excel - Filter Help

Then I answered your question.

See your workbook returned.

Company, Department and Subdepartment all have corrected DVL formulas using Range Names defined in the Name Manager.

The other 4 have yet to have names defined and formulas corrected to insert in each of their DVL.

You will see that in operation, there are features that can't be realized using just spreadsheet functionality.

Ideally, when a Company Selection changes, then ALL dependent DVL SELECTIONS ought to be CLEARED. Can't do that with spreadsheet functions. Which is why I most often used queries in VBA procedures.

But there could be another method that I'm not familiar with. Maybe combo or andy or someone else have another idea.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Excel - Filter Help

What about Power Query in Excel? That should get this job done.
Or - maybe Excel is not the answer? How about a relational database, like Access?

---- Andy

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

RE: Excel - Filter Help

(OP)
Hi Skip
The returned file seemed like the original file without the names defined yet.

Thanks,
arv

RE: Excel - Filter Help

(OP)
Thanks Skip for all your help. Much appreciated.
I have also tried to create the DVL using the filter/isnumber/search function which gives me the option to search within the DVL.
However, not able to correlate the relationships between Company/Department/Sub Department.

Thanks,
Arv

RE: Excel - Filter Help

Quote:

I have also tried to create the DVL using the filter/isnumber/search function which gives me the option to search within the DVL.

What do you mean?

The Department drop down displays the Department values for the Selected Company.

Likewise the Sub Department drop down displays the Sub Department values for the Selected Company.

That's all it does.

Did you have some other expectation?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Excel - Filter Help

(OP)
Hi Skip
Your solution is good.

What i meant was i was trying to use the filter/isnumber/search function to provide the search list feature.
However, unable to make it work in the DVL.

As usual, Thanks Heaps for your help!

Thanks,
arv

RE: Excel - Filter Help

I have no idea what you mean.

Please explain in detail, using the data in your workbook, what you are searching for and what end result you intend to produce.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Excel - Filter Help

(OP)
Hi Skip
I have tried to use the filter formula on the sub department worksheet.
However:
1. The formula has to be entered outside the table
2. I can't figure out if its possible to use the filter function in the DVL

The aim is to:
1. Create a dependent drop down list (as per your solution)
2. Drop Down List - Incorporate search feature (as per behavior when you type into an autofilter)

Thanks,
arv

RE: Excel - Filter Help

Arv, the Data Validation Drop Down List is pretty simple but its purpose is to keep invalid data out of a cell.

What you seem to be looking for is not possible in a DVL.

You might look at ActiveX Combobox and Listbox controls, but now you're talkin'bout VBA and posting in forum707: VBA Visual Basic for Applications (Microsoft). I have next to ZERO recallable memory of this level.

BTW, just typing into a cell column in a Structured Table, Excel displays a popup that functions with a search of the string you are entering.

Ever notice that? Sort of does what you want, I think. Go to the Company sheet and in the Description column, type C in the first empty cell. Up pops your companies starting with C. In fact, it doesn't even have to be a Structured Table, although STs have so many great useful features.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Excel - Filter Help

(OP)
Hi Skip
Thanks for all your help. Much apppreciated for providing the solution.


Thanks,
arv

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close