Summarise Blank Columns
Hi, I have a sheet that tracks onboarding actions for new employees with a new row for each employee and the actions required as columns. It's getting a bit wide and messy though and isn't easy to tell what actions are outstanding for each employee. Can anyone think of a clever way summarising outstanding actions - basically collating what columns are blank for each row?
Best Answers
-
Here's an idea:
Create a column called something like "Actions Remaining" and make it a multi-select column. In the dropdown choices list, list your Action column names in the order you'd like them to appear when present (alphabetical, or by the order they are in left to right across the sheet, etc.)
Then you're going to create a long formula to evaluate each Action cell in the row. If it's blank, this will list the Action name followed by a carriage return. (Note: CHAR(10) is a formula that inserts a carriage return, and in a multi-select cell, effectively separates values.)
=IF(ISBLANK(A-Action@row), "A-Action" + CHAR(10), "") + IF(ISBLANK(B-Action@row), "B-Action" + CHAR(10), "") + IF(ISBLANK(C-Action@row), "C-Action" + CHAR(10), "") + (...keep going with IF statements for all your Action columns)
After adding your formula and making it a column formula, lock the Actions Remaining column.
When you add values to the action cells on a row, the cell name will disappear from the Actions Remaining column.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thanks Jeff for your excellent suggestion. Have done what you suggested and it's working great.
Answers
-
Here's an idea:
Create a column called something like "Actions Remaining" and make it a multi-select column. In the dropdown choices list, list your Action column names in the order you'd like them to appear when present (alphabetical, or by the order they are in left to right across the sheet, etc.)
Then you're going to create a long formula to evaluate each Action cell in the row. If it's blank, this will list the Action name followed by a carriage return. (Note: CHAR(10) is a formula that inserts a carriage return, and in a multi-select cell, effectively separates values.)
=IF(ISBLANK(A-Action@row), "A-Action" + CHAR(10), "") + IF(ISBLANK(B-Action@row), "B-Action" + CHAR(10), "") + IF(ISBLANK(C-Action@row), "C-Action" + CHAR(10), "") + (...keep going with IF statements for all your Action columns)
After adding your formula and making it a column formula, lock the Actions Remaining column.
When you add values to the action cells on a row, the cell name will disappear from the Actions Remaining column.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thanks Jeff for your excellent suggestion. Have done what you suggested and it's working great.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives