Forum Discussion
Calculated Columns and Filters
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
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
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-4731-8f9b-3dfaeacea3d4#OfficeVersion=Online
Regards,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")
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
