Conditional Formatting Metrics
I have a sheet with a lot of conditional formatting to identify when training and license dates in my sheet are coming close to expiration or have expired. The sheet has about 15 columns, each for a separate training or licensing completion date, and conditional formatting is looking at those dates to determine when it's past a certain number of days to shade it a certain color.
I'm now trying to generate a report or metrics to show how many people are within 180 days of expiration, 90 days within expiration, expired, etc. I know I can do this with formulas, but since I already have it in conditional formatting I wanted to ask if anyone knows of a way to generate the total number of cells that meet the criteria of a piece of conditional formatting logic. If I could get that number I could calculate the metrics I need, otherwise I think I'll have to create helper columns determining the number of dates to expiration, and then use a summary field to get a count of the number of records meeting the criteria of how close they are to expiration.
Answers
-
From all my research I do not believe that is possible it would be a great addition. I have wanted to do something similar myself.
-
@Bethany Garcia At the risk of being obvious, are you building a report of the sheet, or are you generating metrics? If the latter, where do you plan to make these metrics visible? I think the approach depends on your final desired outcome. I don't think you can count rows based on conditional format rules.
I would probably use summary fields to count rows for your various conditions. (Not sure you need extra columns to help with that, although I often do employ such columns.) Then you could produce a summary report and display the results on a dashboard.
Depending on how the sheet is laid out, you might be able to use a metric widget directly (doesn't seem like it from your description ... )
dm
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 430 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!