Hello!

MBMaddox

I'm relatively new to using Smartsheet, and I have a question regarding a formula I'm trying to create. My colleagues and I are tracking our steps for a work event, and I've set up a sheet and a form for them to submit their work email addresses, dates, and total steps. What I want to achieve is a formula that can automatically search for their email and sum their steps, which I plan to use in a Dynamic View to maintain privacy.

I've managed to create a sum formula using the following: =SUMIF({GIG Challenge Tracker Range 1}, "testemail@fakemail.com", {GIG Challenge Tracker Range 2}). However, I've been manually revieweing the submissions and creating a new formula for each new email address that is submitted. I'd prefer not to manually add email addresses to the formula. Is there a way to create a formula that can automatically identify and include new email addresses submitted in the sheet and then calculate the total steps for each person?

I initially attempted to create a report and group the submitted steps by email address, then summarizing the steps for that email address, but unfortunately, grouping doesn't display correctly in Dynamic View. I apologize if my explanation wasn't very clear, and I appreciate any help you can provide.

Nat

I've been asked to create a similar thing, and found it was getting complex when using a form, as each form entry is a new row.

Rather than deal with all of this (and for the sake of using this as a template for future non-formula minded individuals) I was thinking I might create a sheet with a row for each of the participants, include a contact column for the email, which office or team they're in, and a column for each day of the challenge as well as their total steps summed up.

Then I'd create a "my tasks" report (in this case "My Steps") so they can all receive the same link in an email, but each can only see their own row when they click it, and can input their own steps under each date.

They'll then also get a link to a fun looking live dashboard which the person running it can take a snapshot of for the reminder emails.

I think this could work, I'd be interested to see what you come up with as well!

MBMaddox

@Andrée Starå is this something you could assist with?