Reminder automation

bgarrett

@JamesB It says "invalid operation"?

JamesB

@bgarrett Let's Start with simplifying it to find out where we are getting the error in the formula.

=if({Firm Name}=[Firm Name]@row,1,0)

If the above works then try this,

=if({Market Date}=[Market]@row,1,0)

bgarrett

@JamesB Unfortunately both are giving that error:

=IF({Firm Name DS Intake} = [Firm Name]@row, 1, 0)

=IF({Market Date DS Intake} = [Market Date]@row, 1, 0)

* Fyi - The source/form sheet is called DS Intake so when I cross reference I name things this way.

I can't figure out why, though. Everything looks right?

JamesB

What is your column type for the Submitted? column?

JamesB

@bgarrett

Another though just came to mind. On your form sheet do you have any cells in the column being referenced that have invalid data or an error. This can cause an invalid operation return on a formulas.

bgarrett

Hi, @JamesB !

The "Submitted?" column is text/number. The error remains no matter the column type. There's no invalid data/errors on the form sheet.

I have another sheet that uses index/match to pull specific data from this form sheet, and it has a Row ID column that's the exact same as this one on the form sheet that it uses in its formulas:

I'm not sure if that would help, but maybe? I also didn't set up that other sheet - someone at Smartsheet did it - so I don't know how to replicate that column on this helper sheet.

JamesB

@bgarrett

I just recreated your scenario in my SS and I think this will work.

=COUNTIFS({Market Form Range 1}, [Market Name]@row, {Market Form Range 2}, [Market Date]@row)

As long as your Market Name and Market Date are the same on both sheets, you get a true or 1 result which will check the box, if they do not then it is false or 0 response, which will uncheck the box.

bgarrett

@JamesB That worked!!! I'm currently testing an automation to make sure that works properly, and I'm double checking some of the markets' schedules, but the helper sheet is set up and works great. I'll reach out if I run into any other road blocks. You have been incredibly helpful and I am SO appreciative!!