Report - Return the Column of the cells with a certain value

emm86 ✭✭
edited 12/09/19 in Smartsheet Basics

Hi All,

I have a sheet that I am using to track the results of site inspections. Each row is a facility's inspection, each column heading is a question on the assessment (ex. have fire extinguishers been inspected?) and each cell has an option of "yes", "no", or "Found not Fixed".

I want to report on this "Found not Fixed" option. I am trying to get my report to return all of the assessment questions that have a response of "Found not Fixed". When I select my sheets and all of the column headings, then select "found not fixed" in the report builder it doesn't return anything. When I select just one column heading at a time, is does return a result. Is there a way to have it look at all the column headings and return all of "Found Not Fixed" results?

Please see screenshots for reference

SS Sheet.png

SS Report.png



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Unfortunately not. The only way to do it is one column at a time in the report builder.

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Option 1: Use the Report Builder

    ... It appears you started down that path. Select each column in turn and add it the the What? criteria. The default for a new criterion is AND.

    Clicking on any of the bars that indicate "and" will change them ALL to "or", which is what you want.

    If you ever need to do isn't all ANDs or all ORs, like (this and that or (this other thing and something else), then you'll need to build a formula to capture it.

    Option 2: Build a formula.

    A variety of ways to do this, up to and including only returning the column name(s) of the column(s) affected for each row. Those can be quite complicated (and you can't access the column name directly so I sometimes have one of the first rows reproduce them for me)

    Use Option 1.

    I hope this helps.