Calculated Columns and Filters

[Amapola67]

Good Afternoon

I have to add complexity to a staff list I'm keeping as there's a another company in the mix now.

I used to have a display "Active" that simply showed everybody with the Status Active or Future. Now I also want to limit that view to just one company.

I've added a helper column but whatever I try, it doesn't work.

AND(OR(Status="Active",Status="Future"),Company="SHS" - results in Yes or No displayed in the list as intended. But if I filter by Yes, Sharepoint indicates it filters to 1 but nothing shows.

If I try to change the formula to an IF formula to give me a better way to filter, 

=IF(AND(OR(Status="Active",Status="Future"),Company="SHS")=1,"True","FALSE")

will only display FALSE. Despite the condition essentially is the same as above, suddenly the result is totally different.

• Trying to filter on the first formula result doesn't work, i.e. doesn't display content
• Using this formula within another formula also doesn't work

Where is the logic here? Any alternative suggestions?

Thanks, Christine

[#2]

Hi @Amapola67 

is there any reason you can't create/modify the view and apply filter conditions in the view itself to apply your filter rules instead of depending on a calculated field?

See Create or modify a filtered view based on column indexes chapter in this article https://support.microsoft.com/en-us/office/use-filtering-to-modify-a-sharepoint-view-3d8efc52-0808-4... 

Regards,

[#3]

@luis-ribeiro 

I need 3 filters and a view is limited to 2. Which means I need a helper column.

I've worked it out, the formula was incorrect. The -1 is not required.

=IF(AND(OR(Status="Active",Status="Future"),Company="SHS")=1,"True","FALSE")

This works:

=IF(AND(OR(Status="Active",Status="Future"),Company="SHS"),"True","FALSE")

[#4 · Accepted solution]

Hi @Amapola67

if you found the solution, perfect. But as it concerns to lists views, you can have a large number of filters applied.
You can set them on the "modern view" or you can go to the list settings > edit the view > filters option.

Regards

[#5]

@luis-ribeiro 

Show More Columns...

I never noticed that before! Thank you!