Summarise Blank Columns

s_mawer

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?

Jeff Reisman

@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.

s_mawer

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

Jeff Reisman

@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.

s_mawer

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