Summary reporting with conditional formatting


Hello! I'm trying to build out a way to dynamically report on 58 state/territory sheets. Ideally, this report would have the total number of rows in the state sheet, plus the number of rows that meet two separate sets of conditions, with conditional formatting applied. I've tried doing this two ways:

  • By using a sheet with formulas using cross-sheet references (screenshot below). This works great! Except that I've run into Smartsheet's limit of no more than 100 cross-sheet references in a sheet because I need to reference two different columns in each state sheet (whether something is verified or not plus the Created date column), and I have 58 states/territories.
  • By using sheet summary stats and then building a summary report (screenshot below). This is super easy in terms of getting the stats I need, but then I can't do conditional formatting, include other information in the report (e.g., who is assigned to each state), or do a vlookup or anything else to get the data out of this report.

Can anyone think of a way to get what I need - a single place to get a conditionally formatted set of summary stats across 58 sheets, using two data columns?

Thank you!!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!