Formula Question

Options

If range 4 has "Production" and Range 1 doesn't have "OK 3" then return Range 2 =({PDCA Range 4}, HAS(@cell, "Production"), {PDCA Range 1}, FIND("OK 3", @cell) ????????????????

Best Answer

  • Lidiya S.
    Lidiya S. ✭✭✭
    Answer ✓
    Options

    Thank you for the context, this makes it a lot easier to solve your problem! You actually don't need a formula for this at all.

    Instead of creating a separate sheet, create a "Report".

    In the new Report you can use the Report Builder toolbar to filter the information from your original sheet.

    • Click on "Source Sheet" and select the sheet with the issues log
    • Click on "Columns to Display" and select "Description 2"
    • Click on Filter Criteria
    • In the "select field" choose "Team Responsible"
    • In the "select type" choose "Is One Of"
    • In the Final field choose "Production"

    Now you are filtering in only the issues with Production as the team responsible.

    • Click on "Add a Condition"
    • Repeat the above steps but now select the "OK/NG" field instead of "Team Responsible", select "Is not one of" and in the final field choose "OK 3"

    Now you have built a report that only shows issues that Production is responsible for and are not "OK 3".

    Tip: If you want to see Description 2 AND the Person Responsible, all you have to do it select the "Person Responsible" column under "Columns to Display" in the report toolbar.


    If you have any other questions, please feel free to reach out! For more info on reports you can go to: https://help.smartsheet.com/learning-track/smartsheet-intermediate/reports.

    Lidiya Shutaya

    lidiya@ddbconsultants.ca

Answers

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭
    Options

    Hi Brandon,

    Would including the NOT Function within the FIND formula work?

    e.g. FIND(NOT({PDCA Range 1} = "OK 3"), @cell)

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work

  • Brandon03
    Options

    Returns a invalid operation =COUNTIFS({PDCA Range 2}, HAS(@cell, "Production"), FIND(NOT({PDCA Range 1} = "OK 3"), @cell))

  • Brandon03
    Options

    If production is found anywhere in in range 2 and OK 3 is not found in range 1 return the data in Range 3 =COUNTIFS({PDCA Range 2}, HAS(@cell, "Production"), {PDCA Range 1}, FIND(NOT({PDCA Range 1} = "OK 3"), @cell) = {PDCA Range 3})

  • Lidiya S.
    Lidiya S. ✭✭✭
    Options

    Brandon03,

    Are you looking at this row by row or actually looking at an entire range? Can you share a little more about your goal for this project?

    The COUNTIF formula will only return the # of times a certain condition occurs, not specific information in a cell. If you want the formula to return specific values you could us an IF formula, but it will only return a specific value, not a range of values.

    If you do this row by row you could use something like:

    =IF(AND(FIND("Production", {PDCA Range 2}) > 0, FIND("OK 3", {PDCA Range 3}) = 0), {PDCA Range 3}, "")

    Best,

    Lidiya Shutaya

  • Lidiya S.
    Lidiya S. ✭✭✭
    Answer ✓
    Options

    Thank you for the context, this makes it a lot easier to solve your problem! You actually don't need a formula for this at all.

    Instead of creating a separate sheet, create a "Report".

    In the new Report you can use the Report Builder toolbar to filter the information from your original sheet.

    • Click on "Source Sheet" and select the sheet with the issues log
    • Click on "Columns to Display" and select "Description 2"
    • Click on Filter Criteria
    • In the "select field" choose "Team Responsible"
    • In the "select type" choose "Is One Of"
    • In the Final field choose "Production"

    Now you are filtering in only the issues with Production as the team responsible.

    • Click on "Add a Condition"
    • Repeat the above steps but now select the "OK/NG" field instead of "Team Responsible", select "Is not one of" and in the final field choose "OK 3"

    Now you have built a report that only shows issues that Production is responsible for and are not "OK 3".

    Tip: If you want to see Description 2 AND the Person Responsible, all you have to do it select the "Person Responsible" column under "Columns to Display" in the report toolbar.


    If you have any other questions, please feel free to reach out! For more info on reports you can go to: https://help.smartsheet.com/learning-track/smartsheet-intermediate/reports.

    Lidiya Shutaya

    lidiya@ddbconsultants.ca

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!