Formula help please!

Tracey Tume

I have put this formula into a text/number cell and it is working on all lines EXCEPT the lines that match "To Aim Spraypainters" and have the Install/Pickup date prior to today. I have checked that the spelling of the text matcheS the column heading and its correct.

=IF(OR([Factory Completed]@row = 1, AND(Description@row = "To Aim spraypainters", [Install/Pickup date]@row < TODAY(-1))), -[Total production time]@row, 0)

Any help will be gratefully received 🙂

Genevieve P.

Hi @Tracey Tume

Thank you for this screen capture, this is really helpful!

In the image I can see that your Factory Completed column has a formula as well, which is where the NO MATCH is coming form. Formula errors are like dominoes: if one of your formulas is referencing a cell with an error, it will show that same error.

Try wrapping an IFERROR around the formula you have in the Factory Completed column:

=IFERROR(formula, "")

This should then resolve the issue with your other formula!

Cheers,

Genevieve

Genevieve P.

Hi @Tracey Tume

Is it not working for the dates that are specifically yesterday?

TODAY(-1) is yesterday, and you're looking for a date prior to that, meaning it has to at least be 2 days ago. Try either adding an = sign so it also includes yesterday:

[Install/Pickup date]@row <= TODAY(-1)

Or look for TODAY() without a number:

=IF(OR([Factory Completed]@row = 1, AND(Description@row = "To Aim spraypainters", [Install/Pickup date]@row < TODAY())), -[Total production time]@row, 0)

Let me know if that did the trick!

Cheers,

Genevieve

Tracey Tume

Thank you Genevieve

I had tried those options before, great minds think alike, but no luck.

Do you think it matters if the Description column is a drop down column? I have used similar references to the Description column before in other formulas and its worked so cannot figure it out.

Thanks for your help.

Tracey

Genevieve P.

Hi @Tracey Tume

Ah, thanks for confirming.

The dropdown column shouldn't be an issue...although it may have to do with the text that's coming through that dropdown. I'd check is to make sure you're searching for exactly what's put in the dropdown (could it be spelled slightly different of have an extra space?)

Try opening the Column Properties to copy the text phrase, then past that exact phrase into your formula:

=IF(OR([Factory Completed]@row = 1, AND(Description@row = "To Aim Spraypainters", [Install/Pickup date]@row < TODAY(-1))), -[Total production time]@row, 0)

Any luck? If not, it would be helpful to see a screen capture of your sheet identifying the column types - I'll re-create it on my end exactly as you have it to see what may be going on.

Thanks!

Genevieve

Tracey Tume

Hi Genevieve

I have already checked that the spelling was correct, did it exactly how you suggested.

please see doco attached for properties of columns etc

Smartsheet Community Query.docx

Genevieve P.

Hi @Tracey Tume

Thank you for this screen capture, this is really helpful!

In the image I can see that your Factory Completed column has a formula as well, which is where the NO MATCH is coming form. Formula errors are like dominoes: if one of your formulas is referencing a cell with an error, it will show that same error.

Try wrapping an IFERROR around the formula you have in the Factory Completed column:

=IFERROR(formula, "")

This should then resolve the issue with your other formula!

Cheers,

Genevieve

Tracey Tume

Morning Genevieve

That did the trick! Wonderful, thank you so much for your help!

Tracey