Using both AND/OR in Report Builder
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.
Best Answers
-
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)
-
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:
Have a fantastic weekend!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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)
-
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)
-
That makes sense now, thank you!
-
Happy to help! 👍️
-
I saw that Paul answered already!
Let me know if I can help with anything else!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
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:
Have a fantastic weekend!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives