Advanced Search Filtering

[Treetops11]

Hi,

I'm after some assistance adding further search filters to my search function.

At the moment search has been implemented and is working well. However I want to be able to implement additional check box filtering.

Text input into the 'search by' would therefore be dependant on whether the checkbox was ticked ("yes") or not ticked "no". I have not yet input the checkboxes.

For example I want to be able to tick the checkbox for 'checkboxcriteria1', which would mean that the table would filter to only those with "yes", then I want to be able to perform an additional text search on top of that using the search by inputs which I have already setup.

The only code I currently have is from the table seen in the image which is known as 'data table'.

The code is as follows:

=FILTER(Data_table,ISNUMBER(SEARCH(B8,Data_table[Year]))*ISNUMBER(SEARCH(B4,Data_table[Sector]))*ISNUMBER(SEARCH(B5,Data_table[Client]))*ISNUMBER(SEARCH(B6,Data_table[Project]))*ISNUMBER(SEARCH(B7,Data_table[Tags])),"NO MATCH FOUND")

Please let me know how I can add the conditional filtering with checkboxes into this code.

Thanks

 

[jasonb75]

For each of the search sections in the formula you would need to change

ISNUMBER(SEARCH(B4,Data_table[Sector]))

to

IF(C4,ISNUMBER(SEARCH(B4,Data_table[Sector])),1)

Where C4 refers to the link cell for the checkbox associated with the search string in B4.

 

[Treetops11]

T

For each of the search sections in the formula you would need to change

ISNUMBER(SEARCH(B4,Data_table[Sector]))

to

IF(C4,ISNUMBER(SEARCH(B4,Data_table[Sector])),1)

Where C4 refers to the link cell for the checkbox associated with the search string in B4.

Thanks for the response however unfortunately did not work.

I have blocked out the sensitive data but as you will see the tick boxes made no difference to the way the table filtered unfortunately, code shown below:

=FILTER(Data_table,ISNUMBER(SEARCH(B8,Data_table[Year]))*IF(D4,ISNUMBER(SEARCH(B4,Data_table[Sector])),1)*IF(D5,ISNUMBER(SEARCH(B5,Data_table[Client])),1)*IF(D6,ISNUMBER(SEARCH(B6,Data_table[Project])),1)*IF(D7,ISNUMBER(SEARCH(B7,Data_table[Tags])),1),"NO MATCH FOUND")

Note: there is no checkbox for b8 so was left blank.
Note: In my main table i changed all "yes" to "True" and "no" to "False" however did not help.

As you will see in the image, ticking all 4 as true did not filter the table as I was hoping. I would have expected to see only one result before I even used my search box functionality as there is only one line with 4 'True'.

 

[jasonb75]

edit:-

I misread your question and thought that the checkboxes related to the search boxes.

Go back you your original formula, then add a section for each checkbox (this is with "Yes" or "No" in the table. This goes in front of the last comma in your formula, repeat for each checkbox,

*IF(D4=TRUE,Data_table[CheckBoxCriteria1]="Yes",Data_table[CheckBoxCriteria1]="No")

 

[Treetops11]

edit:-

I misread your question and thought that the checkboxes related to the search boxes.

Go back you your original formula, then add a section for each checkbox (this is with "Yes" or "No" in the table. This goes in front of the last comma in your formula, repeat for each checkbox,

*IF(D4=TRUE,Data_table[CheckBoxCriteria1]="Yes",Data_table[CheckBoxCriteria1]="No")

I have tried each of the following based on your description however unfortunately no success:

=FILTER(Data_table,ISNUMBER(SEARCH(B8,Data_table[Year]))*ISNUMBER(SEARCH(B4,Data_table[Sector]))*ISNUMBER(SEARCH(B5,Data_table[Client]))*ISNUMBER(SEARCH(B6,Data_table[Project]))*ISNUMBER(SEARCH(B7,Data_table[Tags]))*IF(D4=TRUE,Data_table[CheckBoxCriteria1]="Yes",Data_table[CheckBoxCriteria1]="No")*IF(D5=TRUE,Data_table[CheckBoxCriteria1]="Yes",Data_table[CheckBoxCriteria1]="No")*IF(D6=TRUE,Data_table[CheckBoxCriteria1]="Yes",Data_table[CheckBoxCriteria1]="No")*IF(D7=TRUE,Data_table[CheckBoxCriteria1]="Yes",Data_table[CheckBoxCriteria1]="No")"NO MATCH FOUND")

=FILTER(Data_table,ISNUMBER(SEARCH(B8,Data_table[Year]))*ISNUMBER(SEARCH(B4,Data_table[Sector]))*IF(D4=TRUE,Data_table[CheckBoxCriteria1]="Yes",Data_table[CheckBoxCriteria1]="No")*ISNUMBER(SEARCH(B5,Data_table[Client]))*IF(D5=TRUE,Data_table[CheckBoxCriteria1]="Yes",Data_table[CheckBoxCriteria1]="No")*ISNUMBER(SEARCH(B6,Data_table[Project]))*IF(D6=TRUE,Data_table[CheckBoxCriteria1]="Yes",Data_table[CheckBoxCriteria1]="No")*ISNUMBER(SEARCH(B7,Data_table[Tags]))*IF(D7=TRUE,Data_table[CheckBoxCriteria1]="Yes",Data_table[CheckBoxCriteria1]="No"),"NO MATCH FOUND")

 

[jasonb75]

Both formulas look correct (first one is missing a comma before "NO MATCH"), I don't think that you need to array confirm it with office 365 but you could try doing that to see if it makes a difference.

If it is not working then my best guess would be that there is nothing in the data matching all of the criteria.

 

[Treetops11]

Both formulas look correct (first one is missing a comma before "NO MATCH"), I don't think that you need to array confirm it with office 365 but you could try doing that to see if it makes a difference.

If it is not working then my best guess would be that there is nothing in the data matching all of the criteria.

I even tried changing the check box names to match the cells as I was pasting them all as 'checkbox1' when there are actually four checkboxes, still no luck.

Even when I use just the following on its on I still get an error:

*IF(D4=TRUE,Data_table[CheckBoxCriteria1]="Yes",Data_table[CheckBoxCriteria1]="No")

Full formula being:

=FILTER(Data_table,ISNUMBER(SEARCH(B8,Data_table[Year]))*ISNUMBER(SEARCH(B4,Data_table[Sector]))*ISNUMBER(SEARCH(B5,Data_table[Client]))*ISNUMBER(SEARCH(B6,Data_table[Project]))*ISNUMBER(SEARCH(B7,Data_table[Tags]))*IF(D4=TRUE,Data_table[CheckBoxCriteria1]="Yes",Data_table[CheckBoxCriteria1]="No"),"NO MATCH FOUND")

This tells me maybe Im still not using this correctly?

 

[jasonb75]

What do you get if you try

=FILTER(Data_table,IF(D4=TRUE,Data_table[CheckBoxCriteria1]="Yes",Data_table[CheckBoxCriteria1]="No"),"NO MATCH")

Note that with the full formula, it will return rows where 'ALL' of the criteria are met, not 'ANY' of the criteria.

 

[Treetops11]

Unfortunately that did not work.

Instead, can I add one last search box which searches the entire table and returns any matches, no matter the column? eg if I had a table with the "apples" appearing in multiple columns, Id want to be able to search able in this text box and return all results.

This search would not be a dependent search based on the other searches I have.

This issue however that I can see is that this would be a dynamic table and I have the other dependent search boxes tied to the table with the following formula:

=FILTER(Data_table,ISNUMBER(SEARCH(B8,Data_table[Year]))*ISNUMBER(SEARCH(B4,Data_table[Sector]))*ISNUMBER(SEARCH(B5,Data_table[Client]))*ISNUMBER(SEARCH(B6,Data_table[Project]))*ISNUMBER(SEARCH(B7,Data_table[Tags])),"NO MATCH FOUND")

 

[jasonb75]

Unfortunately that did not work.

There is a difference between a formula that 'does not work' and a formula that 'does not do what you want' which is more often the case, if you look at my post you will see that I asked 'what do you get' not 'does it work'. The formula was a shortened version intended only to test the criteria1 checkbox, nothing else. This was to see how the formula behaves with a single checkbox in an attempt to make it easier to debug.