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!!
Stacey
Answers
-
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!
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’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!
-
And by 'advise' I mean 'advice' - I was on my phone 😂
-
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)
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.
-
@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.
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!