Formula Question

Brandon03

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) ????????????????

Lidiya S.

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

Jason Albrecht

Hi Brandon,

Would including the NOT Function within the FIND formula work?

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

Brandon03

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

Brandon03

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.

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

Brandon03

Lidiya S.

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