FILTER Function

[Gesyca_is_joy]

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

 

[jasonb75]

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]

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]

If you type =fil into a cell is Filter one the options that comes up?

 

[Gesyca_is_joy]

If you type =fil into a cell is Filter one the options that comes up?

Yes

 

[Fluff]

Ok, how about

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

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]

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]

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]

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.