Formula for automation help

Options

Hello Smartsheet gurus!

I am trying to automate a step-challenge for teams at work.

Team members submit personal step counts via webform to Sheet 1.

Sheet 1-Column titles: Team Name (drop down), Team Member (free text), Steps Entered (free text)

Numbers submitted are automatically summed on another sheet, as an ongoing tally of each teams step progress on Sheet 2.

Sheet 2-Column Titles: Team Name, Steps total, Steps left to goal

I need a formula that will pull and sum the steps entered via webform on Sheet 1 and display them next to the corresponding Team Name on Sheet 2.

Tags:

Answers

  • Kerry St. Thomas
    Kerry St. Thomas ✭✭✭✭✭
    Options

    Sheet 2 should have Team Name prepopulated, and deduplicated (pretty much the same as the dropdown on Sheet 1).

    With that in place, make the Steps Total column a column formula with this:
    =SUMIF({Sheet 1 Team Name}, [Team Name]@row,{Sheet 1 Steps Entered})

    Mind you, you'll have to manually select the ranges currently listed in the {} sets so that Smartsheet knows what it's supposed to look at - the easiest way to do that is to NOT copy & paste the formula, but to type it - and click "Reference Another Sheet" in the popup when you reach each of the spots the curly brackets are. More info about cross-sheet formulas is here: https://help.smartsheet.com/learning-track/level-3-advanced-users/cross-sheet-formulas

    If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!