Summarise Blank Columns

Options

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

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 02/07/22 Answer ✓
    Options

    @s_mawer

    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!

  • s_mawer
    s_mawer ✭✭
    Answer ✓
    Options

    Thanks Jeff for your excellent suggestion. Have done what you suggested and it's working great.

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 02/07/22 Answer ✓
    Options

    @s_mawer

    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!

  • s_mawer
    s_mawer ✭✭
    Answer ✓
    Options

    Thanks Jeff for your excellent suggestion. Have done what you suggested and it's working great.