Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

How to use the Effort (People) Symbols with Formula

cme14041
cme14041
edited 12/09/19 in Archived 2017 Posts

Hi Smartsheet Community!

 

I have been looking for different help tips on How Tos, on using certain symbols and my search has been coming up very vague.  I can't seem to find anything on how to use the Effort Symbols (The 1-5 people) and/or little in regards the uses of the directional symbols.  I thought maybe I found something giving me a formula on how to use the Effort symbols but something was wrong with the formula.  

Can someone help me with this?  I would like to use 3 check boxes to be automatically controlled by the effort symbols to get it to display when 3 different people have finished what they are needing to do within the sheet.  Basically after each person check their assigned task the effort symbol will display automatically if 1, 2 or all 3 people have checked the appropriate boxes.  

Can this be done?  And if so PLEASE share how so.

Thank you in advance!

Tags:

Comments

  • Hello! 

    Here's what I would do (there may be a better way):

    After the three checkbox columns, add a Text Number column (this can be locked and hidden). Write a COUNTIF Formula to count the number of boxes checked.

    Then, have your Effort Column, and reference the Text column - 

    For example:

    Text Column - 

    =COUNTIF([Checkbox 1]1:[Checkbox 3]1, 1)

    Effort Column:

    =IF([Count Column]1 = 1, "One", IF([Count Column]1 = 2, "Two", IF([Count Column]1 = 3, "Three")))

    Hope this helps,

    Kara

    2017-05-12_15-37-19.png

    2017-05-12_15-37-37.png

  • Unfortunately, this did not work for me.  Only 1 person lights up after clicking my 3 check boxes.  Also, I tried to build the first formula with the count of 3 check boxes and something goes wrong when I tried 3 out of 3 of my check boxes.  I can do 2 check boxes but not 3 with the count column.

    Please assist.

     

  • Unfortunately, this did not work for me.  Only 1 person lights up after clicking my 3 check boxes.  Also, I tried to build the first formula with the count of 3 check boxes and something goes wrong when I tried 3 out of 3 of my check boxes.  I can do 2 check boxes but not 3 with the count column.

    Please assist.

  • Hello, 

    Can you please copy/paste the formula you used?

    Or if you'd like, you can share your sheet to me so I can take a look - 

    [email protected]

    Best,

    Kara

  • For counting the 3 checkboxes I tired using the following

    First I tired 

    =COUNTIF([Add CloudCME]1:[Closeout Folder and PARS Entry Completed]1, 1) 

    Second,  

    =COUNTIF([Add CloudCME]1:[Audit Performance In Practice]1: [Closeout Folder and PARS Entry Completed]1, 1)

    Third,

    =COUNTIF([Add CloudCME] , [Audit Performance In Practice]1, [Closeout Folder and PARS Entry Completed]1, 1)

     

    After that, I tried to use the following for activating the effort column just to see if it would work.

    =IF([Add CloudCME]1 = 1, "One", IF([Closeout Folder and PARS Entry Completed]1 = 2, "Two", IF([Audit Performance In Practice]1 = 3, "Three")))

  • Thank you Kara!  I was able to get it figured out with a coworker.  

This discussion has been closed.