Help using NOT function

Options

I want this column to show "N/A" or be blank if the Resource/Object type contains "guidance." If the type does not contain "guidance" I want it to populate the formula that starts at lower. This shows an incorrect argument set error when entered. Any advice would be great!


=IF(NOT(CONTAINS("guidance", [Resource/Object Type]@row, "N/A"), LOWER(Curriculum@row + ".g" + [Grade/Level]@row + ".m" + Module@row + IF([Concept/Topic/Arc]@row <> "", "." + [Concept/Topic/Arc]@row + IF(Lesson@row <> "", ".l" + Lesson@row)) + "." + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Public Facing Title]@row, " ", ""), CHAR(34), ""), "-", ""))))

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Try this:

    =IF(NOT(CONTAINS("guidance", [Resource/Object Type]@row)), "N/A", LOWER(Curriculum@row + ".g" + [Grade/Level]@row + ".m" + Module@row + IF([Concept/Topic/Arc]@row <> "", "." + [Concept/Topic/Arc]@row, "") + IF(Lesson@row <> "", ".l" + Lesson@row, "") + "." + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Public Facing Title]@row, " ", ""), CHAR(34), ""), "-", ""))))

  • kpierson109
    kpierson109 ✭✭✭
    Options

    I tried that and it says it is unparseable.

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Options

    If you want the result to be "N/A" if [Resource/Object Type] DOES contain "guidance", I don't believe you want to use the NOT() function.

    =IF(CONTAINS("guidance", [Resource/Object Type]@row), "N/A", LOWER(Curriculum@row + ".g" + [Grade/Level]@row + ".m" + Module@row + IF([Concept/Topic/Arc]@row <> "", "." + [Concept/Topic/Arc]@row + IF(Lesson@row <> "", ".l" + Lesson@row)) + "." + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Public Facing Title]@row, " ", ""), CHAR(34), ""), "-", "")))

  • kpierson109
    kpierson109 ✭✭✭
    Options

    That worked! Is it possible to have the cell be blank instead? I don't know if that is a possibility. I also want this to happen if the resource/object type contains page. So either guidance or page. What would that look like if I did that?

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Options

    I think this one will work.


    =IF(OR(CONTAINS("guidance", [Resource/Object Type]@row), CONTAINS("page", [Resource/Object Type]@row)), "", LOWER(Curriculum@row + ".g" + [Grade/Level]@row + ".m" + Module@row + IF([Concept/Topic/Arc]@row <> "", "." + [Concept/Topic/Arc]@row + IF(Lesson@row <> "", ".l" + Lesson@row)) + "." + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Public Facing Title]@row, " ", ""), CHAR(34), ""), "-", "")))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Sorry about that. I forgot to adjust the parenthesis at the very end. There should have been a total of two.


    =IF(AND(NOT(CONTAINS("guidance", [Resource/Object Type]@row)), NOT(CONTAINS("page", [Resource/Object Type]@row))), LOWER(Curriculum@row + ".g" + [Grade/Level]@row + ".m" + Module@row + IF([Concept/Topic/Arc]@row <> "", "." + [Concept/Topic/Arc]@row, "") + IF(Lesson@row <> "", ".l" + Lesson@row, "") + "." + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Public Facing Title]@row, " ", ""), CHAR(34), ""), "-", ""))

  • kpierson109
    kpierson109 ✭✭✭
    Options

    Thank you! This is all so helpful.


    I am trying to build this formula in another column. I want the formula to populate the name using the formula starting at LOWER if the resource/object type contains "page." How would I fix this?

    =IF(CONTAINS("page", [Resource/Object Type]@row, LOWER(Curriculum@row + ".g" + [Grade/Level]@row + ".m" + Module@row + IF([Concept/Topic/Arc]@row <> "", "." + [Concept/Topic/Arc]@row + IF(Lesson@row <> "", ".l" + Lesson@row)) + IF([Product Line]@row = "Prologue", ".pro") + IF(CONTAINS("guidance", [Resource/Object Type]@row), ".tg"))))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    It looks like you have a misplaced parenthesis or two.


    =IF(CONTAINS("page", [Resource/Object Type]@row), LOWER(Curriculum@row + ".g" + [Grade/Level]@row + ".m" + Module@row + IF([Concept/Topic/Arc]@row <> "", "." + [Concept/Topic/Arc]@row, "") + IF(Lesson@row <> "", ".l" + Lesson@row, "") + IF([Product Line]@row = "Prologue", ".pro", "") + IF(CONTAINS("guidance", [Resource/Object Type]@row), ".tg", ""))

  • kpierson109
    kpierson109 ✭✭✭
    Options

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!