Assistance Needed - Formula - #INVALID OPERATION

Jessica Suarez
Jessica Suarez ✭✭✭
edited 03/31/23 in Formulas and Functions

Hello SS Gods,

Assistance is needed.

So I have 3 columns, Ready to Pair, Not Ready to Pair Date and Not Ready to pair + 2 weeks.

The Ready to Pair? field is a drop down list. Once the status "Ready to Pair in 2 Weeks" is selected, I have a workflow set up to record the date in the Not Ready to Pair Date field. I am using it as a helper column, for the Not Ready to Pair + 2 weeks column.

The goal is once "Ready to Pair in 2 weeks" is selected in Ready to Pair? column, then the date is recorded in Not Ready to Pair Date and I would like to capture that date + 14 days in the Not Ready to Pair + 2 weeks column.

I tried the below formula but am receiving the Invalid Operation error.

Is this possible? Is there a better way to do this?

See below screenshots


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try using a cell reference instead of a column reference for that first one.

    =IF([Ready to Pair?@row = "Ready............

  • Jessica Suarez

    Thank you VERY much Paul. Super easy fix.

    While I have you here. I do have a question on another issue.

    In our business program, our admins are paired with advisors. We house dates that they launch with these advisors in two different source sheets. I created a "Last Date Paired" column (shown below) grabbing the latest contract start date, for that admin, with the status active. However this is only grabbing off of this sheet. Ideally, I'd like to grab the latest date out of the two sheets. Is there any way a max collect formula can pick the latest date out of two dates from different source sheets?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You should be able to do something like this:

    =MAX(COLLECT(sheet 1), COLLECT(sheet 2))

    If that doesn't work then you can always do two separate MAX/COLLECTs and wrap them both in another MAX formula.

    =MAX(MAX(COLLECT(sheet 1)), MAX(COLLECT(sheet 2)))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!