Dynamically fill a cell with text

I have included a picture to help tell the scenario, but I'm trying to dynamically populate a cell with the question's name with the highest result. The actual form I'm using has over 160 columns, so I'm trying to find a way to automate the top result(s) with the question's name. The expectation is to eventually scale this to list the top 5 to 10 questions.


Thanks for any help you all can provide!



Best Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    edited 06/29/22 Answer ✓

    @jmcmahan

    The basic idea would be:

    =INDEX(COLLECT([Column2]1:[Column5]1, [Column2]@row:[Column5]@row, @cell = MAX([Column2]@row:[Column5]@row)), 1, 1)

    what do you want to do when there is 2 equal ones?

    You could join them:

    =JOIN(COLLECT([Column2]1:[Column5]1, [Column2]@row:[Column5]@row, @cell = MAX([Column2]@row:[Column5]@row)), ", ")

    If you want this to be a column formula you would do as below:

    =JOIN(COLLECT(INDEX([Column2]:[Column5], 1), [Column2]@row:[Column5]@row, @cell = MAX([Column2]@row:[Column5]@row)), ", ")

  • jmcmahan
    jmcmahan ✭✭
    edited 06/29/22 Answer ✓

    Thanks, @Leibel Shuchat! These formulas gave me the results I needed. I appreciate it very much!

Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    edited 06/29/22 Answer ✓

    @jmcmahan

    The basic idea would be:

    =INDEX(COLLECT([Column2]1:[Column5]1, [Column2]@row:[Column5]@row, @cell = MAX([Column2]@row:[Column5]@row)), 1, 1)

    what do you want to do when there is 2 equal ones?

    You could join them:

    =JOIN(COLLECT([Column2]1:[Column5]1, [Column2]@row:[Column5]@row, @cell = MAX([Column2]@row:[Column5]@row)), ", ")

    If you want this to be a column formula you would do as below:

    =JOIN(COLLECT(INDEX([Column2]:[Column5], 1), [Column2]@row:[Column5]@row, @cell = MAX([Column2]@row:[Column5]@row)), ", ")

  • jmcmahan
    jmcmahan ✭✭
    edited 06/29/22 Answer ✓

    Thanks, @Leibel Shuchat! These formulas gave me the results I needed. I appreciate it very much!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!