Validation Formula

Welcome to the New Smartsheet Online Community


You’ll notice that things have changed a bit. If you need help getting oriented, please take a look at the posts here in the Welcome to the Community category.

Validation Formula

Going crazy.

My current formula

=IF(AND([Time Missed / Used (In Hours)]1 < 19.99),CONTAINS("Not", [Excused/Not Excused]1:[Excused/Not Excused]1, ".25", "1"))) = #UNPARSEABLE

The goal here is to return .25 or 1 into a cell "Totals" if cell "Time Missed / Used (In Hours)]" is less than 19.99 and cell "[Excused/Not Excused]". Default value of cell "Totals" = 0.

IF cell "[Excused/Not Excused]" contains "excused" THAN cell "Totals" =0

IF cell "[Excused/Not Excused]" contains "Not" and cell "[Time Missed / Used (In Hours)" GREATER than 19.99, Cell "Totals" = 1

IF cell "[Excused/Not Excused]" contains "Not" and cell "[Time Missed / Used (In Hours)" is GREATER 1 but less THAN 19.99, Cell "Totals" = .25

Best Answers

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Based on what I read in the post, I feel like maybe the CONTAINS functions should be using the @row reference instead of the column reference.


    =IF([Excused/Not Excused]@row = "Excused", 0, IF(AND([Time Missed / Used (In Hours)]@row > 19.99, CONTAINS("Not", [Excused/Not Excused]@row)), 1, IF(AND([Time Missed / Used (In Hours)]@row < 19.99, [Time Missed / Used (In Hours)]@row >= 1, CONTAINS("Not", [Excused/Not Excused]@row)), 0.25, 0)))


    I also agree that screenshots would be helpful though. If your [Excused/Not Excused] column contains either the option of "Excused" or "Not Excused", the above formula will never display anything other than 0 because "Not Excused" does contain the text of "Excused" which in turn causes the first IF to be true.

  • Thank you all! This helps tons I will let you know if this works!! This should close out my 2019 - 2020 Q1 goal!!!!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help! 👍️

  • Awesome! Let us know how it goes.

  • This worked perfectly. Thank you for the help. Love this community!

  • So glad to hear that! Thanks for the follow-up

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Great!! This community is definitely a good one full of a ton of information.

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

Sign In or Register to comment.