IF(AND formula Assistance with #Invalid Operation error
I am trying to pull in a status to separate grid , Criteria is as follows:
When Initiative is Checked and Milestone is specific name "Tech Deployment", return value in Status column, else leave cell blank.
=IF(AND({Initiative Checkbox} = 1, {Milestone} = "Tech Deployment"), {Status}, "")
Appreciate any assistance anyone can offer.
Answers
-
The formula looks fine. It could be syntax issue. Try this instead -
=IF(AND([Initiative Checkbox]@row = 1, [Milestone]@row = "Tech Deployment"), Status@row, "")
-
Thanks Anupriya, I am linking out to my plan to pull data in to a separate grid. . When I add the @Row, I get #UnParseable
-
Hi @gdb3
The issue here is that you're looking across sheets and evaluating entire columns in an IF statement. How will the cross-sheet reference know what row you want to bring back?
You could use a JOIN/COLLECT to bring in every single status into one cell for all the rows that meet that criteria, like so:
=JOIN(COLLECT({Status}, {Initiative Checkbox}, 1, {Milestone}, "Tech Deployment"), ", ")
Or if you have a unique identifier on each row (ex. if Sheet 1 has a unique Task Name, and this sheet with the formula also has a column with the exact same Task Names) you can use this name as the thing to search for in Sheet 1 and bring back a specific cell based on that name. This would be done via and INDEX(COLLECT formula - see this other Community thread with an example.
An alternative would be to create a Row Report that looks at your main source sheet, filtering rows based on your two criteria.
Cheers!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thanks Genevieve, each of the references was to a single project plan/sheet. I'll review options.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!