IF CONTAINS Issue
I have a formula in my Purchasing Log sheet that will look at the Vendor Name and compare it to Supplier Name in the Approved Supplier List and if the Vendor is in the Approved Supplier List will fill a check box. It works well but I want to add another condition to only check the box if the Supplier Status Column in the Approved Supplier List contains "Approved".
The formula needs updating since I now have vendors on the Approved Supplier List that have been Inactivated. The way the formula currently sits, this check box will be marked even if the vendor has been made inactive. I don't want that to happen.
Here is my current formula
=IF(CONTAINS(Vendor@row, {Approved Supplier List Range 1}), 1)
Thank you in advance for any help.
Best Answer
-
Hey Chris
Do both of the 'Approved' conditions contain the word 'Approve'? If yes, you can also write it as a CONTAINS. Recall that the CONTAINS function will pick up any variation of the search term.
We can use an OR if needed. However, if the CONTAINS solves it, since you're already familiar with that function, that would be the way I would go.
=IF(COUNTIFS({Approved Supplier List Range 1},CONTAINS(Vendor@row, @cell), {Approved Supplier List Supplier Status}, CONTAINS("Approve", @cell))>0, 1)
Answers
-
Hey Chris
=IF(COUNTIFS({Approved Supplier List Range 1},CONTAINS(Vendor@row, @cell), {Approved Supplier List Supplier Status}, "Approved")>0, 1)
This says if the two conditions are found (which means the count will be more than zero), then check the box.
Remember since this is a cross sheet reference, you cannot simply cut and paste - you must build the cross sheet reference manually.
Kelly
-
Kelly
Formula works fantastic. How would I account for Conditionally Approved in the {Approved Supplier List Supplier Status} along with Approved? Is there an OR statement in the formula or would it be adding another CONDITION statement in the formula? I've tried some various ideas but to no avail.
-
Hey Chris
Do both of the 'Approved' conditions contain the word 'Approve'? If yes, you can also write it as a CONTAINS. Recall that the CONTAINS function will pick up any variation of the search term.
We can use an OR if needed. However, if the CONTAINS solves it, since you're already familiar with that function, that would be the way I would go.
=IF(COUNTIFS({Approved Supplier List Range 1},CONTAINS(Vendor@row, @cell), {Approved Supplier List Supplier Status}, CONTAINS("Approve", @cell))>0, 1)
-
Kelly
Using CONTAINS worked perfect. Thank you.
During this endeavor I also discovered making formulas into column formulas. This should alleviate many issues experienced by our organization.
-
Glad it worked, and yes, try to use column formulas whenever possible.
cheers
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.9K Get Help
- 441 Global Discussions
- 153 Industry Talk
- 501 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 79 Community Job Board
- 511 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!