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

  • Anupriya
    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, "")

  • 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.
    Genevieve P. Employee Admin

    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

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!