Summary reporting with conditional formatting

Stacey P.

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!!

Stacey

Andrée Starå

Hi @Stacey P.

What formulas are you using to collect the information?

How many rows of data do you have in each sheet?

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or 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!

Stacey P.

I’m using COUNT, COUNTIF, and COUNTIFS depending on the metric. The sheets vary in length from very short (<5) to ~400 rows and rapidly growing. Any advise would be appreciated!

Stacey P.

And by 'advise' I mean 'advice' - I was on my phone 😂

Andrée Starå

@Stacey P.

Happy to help!

Haha!

I'd be happy to take a quick look.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

charles.osborn64206

@Stacey P. did you get a way to do this? We are running into the same thing. I have a summary report, but i need to conditional format it. Let me know if you came up with anything.

Stacey P.

https://community.smartsheet.com/discussion/comment/303530#Comment_303530

I ended up splitting the underlying sheet into two (states A-M and N-Z), setting up the conditional formatting there, and then combining them into a single report. A little cumbersome, but it worked!