Using both AND/OR in Report Builder

RNelson

I am wondering if it is possible to use both the AND/OR option in the report builder, or if anyone has an easy work around. Essentially I am looking for date to show up when a box is checked AND when the Connectivity colum is "None" OR the CSA/CVA column is "None" as shown below.

Paul Newcome

In the source sheet, you could create a Checkbox type column that contains a formula that would check the box based on your criteria. Then you would build your report to look at this new checkbox column.

=IF(AND([Eric Approval]@row = 1, OR(Connectivity@row = "None", [CSA/ CVA]@row = "None")), 1)

Andrée Starå

@RNelson,

It should work as long as the following is true.

If the formula structure is the same above/below the Formula Autofill will add the formula(s) automatically.

Conditions That Trigger Formula Autofill

You’ll see formulas populate automatically when you type in a newly inserted or blank row that is:

• Directly between two others that contain the same formula in adjacent cells.
• At the topmost of the sheet if it’s above two rows that have the same formula in adjacent cells. (This includes rows inserted from a form.)
• At the bottommost of the sheet if it’s below two rows that have the same formula in adjacent cells. (This includes rows inserted from a form.)
• Above or below a single row that is between blank rows and has formulas.

More info: 

https://help.smartsheet.com/articles/1641473-auto-filling-formulas-and-formatting

Have a fantastic weekend!

Best,

Andrée

Andrée Starå

Hi,

Unfortunately, it's not possible at the moment, but it's an excellent idea!

Please submit an Enhancement Request when you have a moment

As a possible workaround, you could use formulas in so-called helper columns and report on them.

Would that work?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

RNelson

I submitted an enhancement request, but am not sure how the formula would generate the info to the report. Right now I can select to show data if the box is checked AND one of the fields says not none, but it seems I would have to pick one field or the other to report off of (Connectivity is not None or CSA/CVA is not None)

Paul Newcome

In the source sheet, you could create a Checkbox type column that contains a formula that would check the box based on your criteria. Then you would build your report to look at this new checkbox column.

=IF(AND([Eric Approval]@row = 1, OR(Connectivity@row = "None", [CSA/ CVA]@row = "None")), 1)

RNelson

That makes sense now, thank you!

Paul Newcome

Happy to help! 👍️

Andrée Starå

I saw that Paul answered already!

Let me know if I can help with anything else!

Best, 

Andrée 

RNelson

One other question...If I use the formula above is there a way to automatically apply that formula to new rows that are created by someone submitting a form? I am just trying to make the process automated without having to remember to go in and copy the formula down.

Andrée Starå

@RNelson,

It should work as long as the following is true.

If the formula structure is the same above/below the Formula Autofill will add the formula(s) automatically.

Conditions That Trigger Formula Autofill

You’ll see formulas populate automatically when you type in a newly inserted or blank row that is:

• Directly between two others that contain the same formula in adjacent cells.
• At the topmost of the sheet if it’s above two rows that have the same formula in adjacent cells. (This includes rows inserted from a form.)
• At the bottommost of the sheet if it’s below two rows that have the same formula in adjacent cells. (This includes rows inserted from a form.)
• Above or below a single row that is between blank rows and has formulas.

More info: 

https://help.smartsheet.com/articles/1641473-auto-filling-formulas-and-formatting

Have a fantastic weekend!

Best,

Andrée

Paul Newcome

Simplified version of Smartsheet's documentation:

As long as there are at least two rows containing the formula above and/or below the new row, the formula should auto-fill into the new row.