Assistance Needed - Formula - #INVALID OPERATION

Jessica Suarez

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

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

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)))