FILTER Function

Gesyca_is_joy

Board Regular
Joined
Apr 24, 2014
Messages
87
Ok, so this is a follow-up to a previous thread which I thought I had solved and turns out I was just really tired and not paying attention to what I was doing. Apologies for re-introducing the question.

I have 4 tabs worth of information:

1) Site information (Named table: T_Branches)
2)Company contact information (Named Table: T_Profile)
3) Company scope (which is what work types each company can do) (Named Table: T_Scope)
4) Company Span (which is which locations each company can work at) (Named Table T_Span)

Tabs 1 and 2 are unique entries and tabs 3 & 4 enter multiple companies and multiple sites.

I wanted to make a 5th tab with a search so the user can enter a branch, and a work type, from a dropdown box then return the contact information for all companies that meet that criteria but I cannot get it to work!
I've never used this function before; I did confirm I'm on O365. please see attached file with the tables in them. all tables are named and the two search fields are also named.

My Formula: =FILTER(T_Profile[Company Name:],(T_Span[Branch]=S_Branch,"")*(T_Scope[Work Type=S_TicketType),"")
S_Branch is a cell where the user can enter a branch number which is a dropdown of T_Branches[Branch ID]
S_TicketType is a cell where the user can enter a branch number which is a dropdown of a Named list called "L_TicketType" and is the same list used to select work type in each row of T_Scope.

Tables 2, 3, and 4 all have the company name in them, and in tables 3 & 4 those names are a dropdown option from the T_Profile table so there are no mis-spellings.

I have a small file but not sure how to attach to this post
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,611
Office Version
  1. 365
Platform
  1. Windows
I have a small file but not sure how to attach to this post
You can't attach a file, only post a mini sheet using XL2BB

It is impossible to say for certain without seeing it but I would guess that the problem is with the data in your sheet(s). The formula looks fine as long as the names are all correct, one of them appears wrong in your formula but excel would not allow you to enter it with such an error.
 

Gesyca_is_joy

Board Regular
Joined
Apr 24, 2014
Messages
87
You can't attach a file, only post a mini sheet using XL2BB

It is impossible to say for certain without seeing it but I would guess that the problem is with the data in your sheet(s). The formula looks fine as long as the names are all correct, one of them appears wrong in your formula but excel would not allow you to enter it with such an error.
i was hand typing it, sorry; i see that i forgot the closing bracket on the T_Scope[Work Type] field. the full formula is: =FILTER(T_Profile[Company Name:],(T_Span[Branch]=S_Branch,"")*(T_Scope[Work Type]=S_TicketType),"")

Yea I cannot see why it doesn't work! its frustrating. but excel won't even let me enter it into the function bar.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,958
Office Version
  1. 365
Platform
  1. Windows
If you type =fil into a cell is Filter one the options that comes up?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,958
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
Excel Formula:
=FILTER(T_Profile[Company Name:],(T_Span[Branch]=S_Branch)*(T_Scope[Work Type]=S_TicketType),"")
You had an extra ,"" in the 1st criteria
 

Gesyca_is_joy

Board Regular
Joined
Apr 24, 2014
Messages
87

ADVERTISEMENT

It enters, but i get a #VALUE! error; i have confirmed that i have a record that meets both criteria.

is the problem that my FILTER is referencing more than one table?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,611
Office Version
  1. 365
Platform
  1. Windows
i have confirmed that i have a record that meets both criteria.
If the filter function was not finding anything then it would show a #CALC! error, not #VALUE!

Are there any #VALUE! errors in any of the tables? In the vast majority of cases errors which have not been corrected are passed to dependent formulas.
is the problem that my FILTER is referencing more than one table?
Excluding errors in the data source, that is most likely the problem. For it to work, all of the tables would need to have the same number of rows and be in the same order. For example, the 1st row of data in table 1 is related to the 1st row in tables 2 and 3. The 2nd row of data in table 1 is related to the 2nd row of data in tables 2 and 3, and so on.

If the order is different then the formula has no way to link the rows together, although this would simply mean that there is no record found so should cause a #CALC! error with the filter function.

If the number of rows is not the same in all 3 tables then the formula will only be able to link the rows together as described above based on the smallest of the 3 tables. The excess rows on the larger tables will have nothing to link to in the smaller table which will cause an error. A difference in the number of rows between the T+Span and T_Scope tables would definitely cause a #VALUE! error. If they are the same but the number of rows in T_Profile is different then you may get either a #VALUE! or a #CALC! error, I couldn't say for certain without setting up a test file.
 

Gesyca_is_joy

Board Regular
Joined
Apr 24, 2014
Messages
87
If the filter function was not finding anything then it would show a #CALC! error, not #VALUE!

Are there any #VALUE! errors in any of the tables? In the vast majority of cases errors which have not been corrected are passed to dependent formulas.

Excluding errors in the data source, that is most likely the problem. For it to work, all of the tables would need to have the same number of rows and be in the same order. For example, the 1st row of data in table 1 is related to the 1st row in tables 2 and 3. The 2nd row of data in table 1 is related to the 2nd row of data in tables 2 and 3, and so on.

If the order is different then the formula has no way to link the rows together, although this would simply mean that there is no record found so should cause a #CALC! error with the filter function.

If the number of rows is not the same in all 3 tables then the formula will only be able to link the rows together as described above based on the smallest of the 3 tables. The excess rows on the larger tables will have nothing to link to in the smaller table which will cause an error. A difference in the number of rows between the T+Span and T_Scope tables would definitely cause a #VALUE! error. If they are the same but the number of rows in T_Profile is different then you may get either a #VALUE! or a #CALC! error, I couldn't say for certain without setting up a test file.
Ok that is the issue then. t_profile is a list of every vendor and their contact information. T_Span is a list of vendors against the location they can work at. T_scope is a list of vendors against the work types they can perform. So together the Profile table forms a one-to-many relationship with the other two tables using the column “company Name”. And the T_Branches is a list of all the locations which would also be a one to many relationship with the T_Span table using the field “branch name”.
Because my ultimate goal is to upload this data into our vendor management software when we get it I have tried to normalize the data as much as possible and create tables in such a manner that they can be imported into a database. However that is months off and in the interim I have no way of taking these four tables and extracting a specific set of vendors based on the location and work type
 

Gesyca_is_joy

Board Regular
Joined
Apr 24, 2014
Messages
87
I figured out how to make this work!! So excited. (it's a bit of a work-around but this whole solution is until IT gets me the full database) though I would share.

So since FILTER only works on one table at a time, I made two; one to FILTER the T_Span table based off a branch number, one to FILTER the T_Scope table based off the work type. those are both on a hidden tab in the file.

the search fields are in the tab above the T-Profile table, then that table has two extra columns that search the FILTERs and return "yes" or "no" if the vendor appears in either FILTER. that way the user only really works on one tab, they put in the search parameters, then manually filter the table to only see the "yes" results.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,863
Messages
5,766,814
Members
425,379
Latest member
thedoctor00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top