Looking for formula to trigger flag based on multiple conditions across two sheets
Hello, community!
I need help with what I assume will be a rather complicated IF formula. I have a flag that I need to trigger based on a column (Baptist) that has a Harvey ball at a quarter, or half, or three-quarter, or full, plus cross-reference the [Program Name]@row with another sheet to find the corresponding row see if the client name is what is hard-coded, such as {Sheet 2 Client Reference Range} = "Baptist".
This is what I came up with but I get #invalid.
=IF(AND(Baptist@row > "Empty"), IF(AND([Program Name]@row = {Program Testing Master Sheet Range 2}, {Program Testing Master Sheet Range 1} = "Baptist"), 1, 0))
I appreciate your help.
Best Answer
-
Oh my goodness, I completely missed that. Good catch!
=IF(OR(Baptist@row = "Quarter", Baptist@row = "Half", Baptist@row = "Three Quarter", Baptist@row = "Full"), IF(COUNTIFS({Program Name Column}, [Program Name]@row, {Baptist Column}, "Baptist") >0, 1, 0))
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Are you able to provide some screenshots for reference?
-
Hi, @Paul Newcome
Thanks for taking a look at this for me. The first screenshot is of the sheet the formula is going into, but it's huge so I'm only able to depict the Harvey ball section. The formula is going in a flag column further down the sheet. The second sheet is the reference sheet.
-
Hi @Nate H
The way I would do this is to use a COUNTIFS formula to see if a row with your matching criteria exists on your second sheet:
COUNTIFS({Program Name Column}, [Program Name]@row, {Baptist Column}, "Baptist")
If there's a row in your second sheet that has the same Program Name as this current row, and a cell that says "Baptist", then this COUNT will return at least 1. Now you can use this as the second part of your IF statement:
=IF(Baptist@row > "Empty", IF(COUNTIFS({Program Name Column}, [Program Name]@row, {Baptist Column}, "Baptist") >0, 1, 0))
Let me know if this works for you! (And Paul, let me know if I've missed something 🙈)
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Genevieve P. Thanks for the bump. This one must have gotten buried in my notifications.
The only question I have is regarding the bold portion below:
=IF(Baptist@row > "Empty", IF(COUNTIFS({Program Name Column}, [Program Name]@row, {Baptist Column}, "Baptist") >0, 1, 0))
I don't use this style of indicator very often, but are we able to use greater/less than with a "text value"? If so, where would "N/A" count? I would imagine less than "Empty", but...
-
Oh my goodness, I completely missed that. Good catch!
=IF(OR(Baptist@row = "Quarter", Baptist@row = "Half", Baptist@row = "Three Quarter", Baptist@row = "Full"), IF(COUNTIFS({Program Name Column}, [Program Name]@row, {Baptist Column}, "Baptist") >0, 1, 0))
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Genevieve P. Well that's a bummer. I was hoping there have been some update on the back-end. Haha.
-
You guys are geniuses. Thank you so much @Genevieve P. and @Paul Newcome !
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!