Formula to get data from a row in a summary sheet

Options

Hello everyone,

I have a use case and I cannot find how to implement it in Smartsheet.

Here is the use case :

I have a sheet with some rows in which I have a check box

I would like to get in my summary sheet (in the field "Version PAPA") for example the data in the column "Tâches" in which row is checked in the column "PAPA en cours".

Do you have any ideas or suggestions for this need ?

Thank you and have a nice day,

Corentin

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi Corentin,

    I think I misunderstood your need. The formula I provided should count the number of [PAPA en cours] blocks that are checked. You want to accumulate the values in [Tâches] if [PAPA en cours] is checked. Correct?

    That formula would be:

    = JOIN(COLLECT( Tâches:Tâches, [PAPA en cours]:[PAPA en cours], @cell=1), ", ")

    Closer?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Try:

    = COUNTIF( [PAPA en cours]:[PAPA en cours], 1)

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Corentin Dal Farra
    Corentin Dal Farra ✭✭✭✭✭✭
    Options

    Hello @Mark Cronk

    Thank you for your answer,

    I tried the formula, but I only get 1 as the result of the number of check boxes. Maybe I should try with a collect formula or something similar to get the value of the cell in the "Tâches" column.

    I will keep searching and come back if I find a solution,

    Have a nice day,

    Corentin

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi Corentin,

    I think I misunderstood your need. The formula I provided should count the number of [PAPA en cours] blocks that are checked. You want to accumulate the values in [Tâches] if [PAPA en cours] is checked. Correct?

    That formula would be:

    = JOIN(COLLECT( Tâches:Tâches, [PAPA en cours]:[PAPA en cours], @cell=1), ", ")

    Closer?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Corentin Dal Farra
    Corentin Dal Farra ✭✭✭✭✭✭
    Options

    Hi @Mark Cronk

    Yes you are totally right, it is the formula that I was looking fo, thank you !

    I still have to practice with JOIN COLLECT and also the @cell formula that is not easy to understand for me.

    Thank you anyway, have a great day,

    Corentin

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Hi Corentin,

    Excellent. Glad you found a solution. You're into pretty advanced territory and figured it out. You're well on your way to mastering Smartsheet formulas. Thank you for contributing to the Community.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.