Numbering Checked Checkboxes in a Column

I have a table which looks up active people on a shift each day. Is there a formula I can use in a helper column which ranks all the checked boxes from 1-9? This is so I can use these numbers to index match all the people active on shift in another sheet.

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    That's tough to rank, because checked checkboxes always have a "value" of 1 in Smartsheet. You'd need a RowNumber column for the sheet + two helper columns for each column you'd want to rank.

    Helper column 1, "NumberToRank-Monday":

    =IF(Monday@row = 0, 1000, RowNumber@row)

    This says, if the box is unchecked for Monday, set this cell value to 100, otherwise set it to the RowNumber value.

    Helper column 2, "Rank-Monday":

    =RANKEQ([NumberToRank-Monday]@row, [NumberToRank-Monday]:[NumberToRank-Monday], 1)

    The result is the lowest row number where the Monday box is checked is ranked 1, next lowest row number with a checked Monday box is 2, etc.

    That's a lot of work to rank these.

    Tell me more about what you're trying to do with your INDEX/MATCH, maybe there's a way to use what you already have.

    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!