Creating Complex Report

Randy Van Winkle

I have a sheet with multiple columns that have the same drop down. I want a report that shows any row that contains either of 2 values from that drop down "< 2 week supply" or "No Supply" . I got that figured out, but now I want to only show that set of lines filtered by another column for "Division" .

Debbie Sawyer

This would give you a tick box for any of the 8 columns containing either of the 2 options. Then you could just say this column is 1 and Division is X...

=IF(OR([4mil Nitrile Gloves]@row="No Supply",[4mil Nitrile Gloves]@row="< 2 Week Supply",[COVID-19 Disinfectants]@row="No Supply",[COVID-19 Disinfectants]@row="< 2 Week Supply",[Hand Sanitizer]@row="No Supply",[Hand Sanitizer]@row="< 2 Week Supply",[Hand Soap]@row="No Supply",[Hand Soap]@row="< 2 Week Supply",[Paper Towel Supply]@row="No Supply",[Paper Towel Supply]@row="< 2 Week Supply",[Safety Glasses]@row="No Supply",[Safety Glasses]@row="< 2 Week Supply",[Safety Goggles]@row="No Supply",[Safety Goggles]@row="< 2 Week Supply",[Toilet Paper Supply]@row="No Supply",[Toilet Paper Supply]@row="< 2 Week Supply"),1)

Paul Newcome

The benefit would be that one requires much less typing which in turn is less likely to break due to typos, and would be much easier to update if anything needs to be tweaked in the future.

I did some testing for your other question...

Using a COUNTIFS and directly referencing data that includes "<" such as "< 2 Week Supply" doesn't work. Odd, but...

FIND and CONTAINS can both register the "<" just fine.

So instead of

=COUNTIFS({Range}, "< 2 Week Supply")

try this

=COUNTIFS({Range}, CONTAINS("< 2 Week Supply", @cell))

That should get you your counts.

Debbie Sawyer

@Randy Van Winkle

With your comment about which formula has benefits over the other, I would say it really doesn't matter how you get to an answer as long as you understand the syntax involved in the formula (others would disagree!)

Pauls version is easier to type, a lot shorter and less prone to typos; but it relys on you keeping the columns within the range in order. Which is absolutely fine if your sheet definition is complete and you are satisfied that there are no more columns to add and you are happy with their placement. However, if you re-order your columns within the sheet, this function will keep the range in the formula as any column inbetween the first column defined to the last column defined irrespective of where the first and last end up and and what is inbetween. (So if you move columns around or add others which might use the same drop down values they would be included within your range)

My version is harder to type and prone to typos (although I used copy and paste a lot to help eradicate this) and even though it is longer, it wouldn't matter if the sheet is manuipulated after its creation. The columns could move and have others inserted in between them and it wouldn't effect the result.

Horses for courses. One isn't any better than the other as they both give you what you require. My main point would be use the version that you can follow and can maintain long term. 😉

Have fun with Smartsheet :)

Kind regards

Debbie

Debbie Sawyer

Hi

Within the Report Builder use the What button and select the column that contains the first 2 options that you require; use the criteria definer to place ticks against "< 2 week supply" and "No Supply" you will see that these are summarised with an or between them. Then Click on the What button again and this time select Division and use the criteria definer in there to set the division required. You should see the word AND in a grey bar between your two column criterias in the Report Builder. If you click RUN this should do as you require.

Hope this makes sense and helps...

Kind regards

Debbie

Randy Van Winkle

I can only seem to get all OR or all And. If I change the last item for Division to And, all the ones above change from OR to And

Randy Van Winkle

There are 5 columns that I need to select "< 2 week supply" and "No Supply" prior to selecting Division

Debbie Sawyer

Hi

Yes - I can understand that as it has happened to me lots, but before I answered to that effect I ran a quick test on a dummy sheet and was able to get a mix!

If you can see in this screen shot it is doing what you wanted (to a degree!) Machine Center is a dropdown list column and Column5 is just text.

If this isn't available to you, then I would suggest you set up a checkbox helper column with an IF function in it which will tick the box if the row meets the criteria needed for your report. Then your report just find all the records with a tick in the box.

Will this approach work for you?

Kind regards

Debbie

Debbie Sawyer

@Randy Van Winkle

Are you able to capture a screen shot with the columns and detail the criteria at all? Maybe we could help you with the Checkbox solution? You can nest many statements in IF function. Happy to help if I can...

Debbie

Randy Van Winkle

The problem arises when you add multiple columns of the Or function then try to add And for the "division". All the Or's change to And

Debbie Sawyer

Ahh Yes I see. There is no way around that I'm afraid. I think you really will need that helper column. Are you happy to write this IF function required or would you like me to have a go at it for you?

Randy Van Winkle

I don't think a check box would resolve the issue. I have about 10 divisions to create reports for, all of the information is dynamic and I have about 50+ collaborators.

Randy Van Winkle

So the IF function would combine the "< 2 week supply" and "No Supply"  information into a single column? I think that would work. What more info can I provide to help build that?

Randy Van Winkle

Here are the column names. Check any row that shows any one of these at either value  "< 2 week supply" or "No Supply"

Debbie Sawyer

This would give you a tick box for any of the 8 columns containing either of the 2 options. Then you could just say this column is 1 and Division is X...

=IF(OR([4mil Nitrile Gloves]@row="No Supply",[4mil Nitrile Gloves]@row="< 2 Week Supply",[COVID-19 Disinfectants]@row="No Supply",[COVID-19 Disinfectants]@row="< 2 Week Supply",[Hand Sanitizer]@row="No Supply",[Hand Sanitizer]@row="< 2 Week Supply",[Hand Soap]@row="No Supply",[Hand Soap]@row="< 2 Week Supply",[Paper Towel Supply]@row="No Supply",[Paper Towel Supply]@row="< 2 Week Supply",[Safety Glasses]@row="No Supply",[Safety Glasses]@row="< 2 Week Supply",[Safety Goggles]@row="No Supply",[Safety Goggles]@row="< 2 Week Supply",[Toilet Paper Supply]@row="No Supply",[Toilet Paper Supply]@row="< 2 Week Supply"),1)

Randy Van Winkle

All right, let me give it a shot! Thanks for your help. Will reply with result in the am.

Debbie Sawyer

Phew! I was holding on in there for you, but to be honest it is twenty past midnight here and my eyes are struggling to stay open!! I'll look for an update tomorrow :)

Good luck :)

Randy Van Winkle

Thanks for your help! I really appreciate it.

Paul Newcome

This IF can actually be simplified GREATLY. Basically you want to check a box if any cell within the range (that row) contain either "< 2 Week Supply" or "No Supply".

=IF(OR(CONTAINS("< 2 Week Supply", [Safety Glasses]@row:[Toilet Paper Supply]@row), CONTAINS("No Supply", [Safety Glasses]@row:[Toilet Paper Supply]@row)), 1)

Just use a CONTAINS function to look across the row for "< 2 Week Supply" and another for "No Supply", drop them into an OR and have an output of 1 to check the box if either are true.