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

 

[SkipVought]

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...
[tt]
=UNIQUE(Table1[Company])
[/tt]
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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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!

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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
Much appreciated. Tried copying the formula into the Data Validation/List but came up with an error.

Thanks,
arv

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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]

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

 

[SkipVought]

Please explain how you want to search.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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
Similar to autofilter.

Thanks,
Arv

 

[SkipVought]

Please relate this to your workbook.

You select a Company in the DVL. Now what?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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!

 

[Andrzejek]

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

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

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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!

 

[Andrzejek]

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

 

[arvarr]

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

Thanks,
arv

 

[SkipVought]

Sorry I picked the wrong file.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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]

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

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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
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

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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
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

 

[SkipVought]

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. 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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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
Thanks for all your help. Much apppreciated for providing the solution.


Thanks,
arv