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.

Find more posts tagged with

Accepted answers

All comments

Anupriya

The formula looks fine. It could be syntax issue. Try this instead -

=IF(AND([Initiative Checkbox]@row = 1, [Milestone]@row = "Tech Deployment"), Status@row, "")

gdb3

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

Genevieve P.

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

gdb3

Thanks Genevieve, each of the references was to a single project plan/sheet. I'll review options.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.