What would be the best template to use for this kind of data?

KaraMarv

So my company currently uses an excel file to create what we call "cover sheets" that keeps track of the volume of what is being built for the daily schedule. The sheet is printed out and then scanned to a few other departments as the top sheet, or cover sheet, for the packet. My goal is to transfer this out of Excel and into Smartsheet. My question is if anyone has any suggestions about the best way to do this or which template I could possibly use? I was thinking of just using a sheet and adding the columns and such but I would also need to add a few formulas in order to have a total amount generate for each day (see bottom of screenshot for each total). I have attached a screenshot of what our current cover sheet in excel looks like for reference. If you need any clarification, please let me know.

Any help would be much appreciated! Thank you in advance!

MariaCurtis

At first, I was thinking you could just create a sheet and then use the sum functionality in reports to get your totals (see below attempts at this):

Sheet Attempt 1:

Report Attempt 1:

But then I noticed that you're not straight summing each column, so this functionality isn't that useful, since you can't write your own formulas in the report summaries.

So, I would probably handle it by either summing directly in the sheet like below and having some locked rows at the top with the formulas, OR you could create a separate metrics sheet if you're ok with the data and the totals living in separate places.

Happy to brainstorm more with you if this doesn't solve your use case.

KaraMarv

Thank you for this, Maria!

I really like the idea of having 2 locked cells up at the top that keep the count. I have a couple questions with that:

• How do you create locked cells like that? And do they stay at the top of the sheet the more data you enter?
• Do those locked cells stay there at the top if you use a filter? For example, say I wanted to look at just what was happening for Monday 2/26 schedule. Could I filter the rest out and still get that count at the top with looking at just Monday's data?

Thank you SO much for your help!

Andrée Starå

Hi @KaraMarv

I hope you're well and safe!

To add to Maria's excellent advice/answer.

• You could probably use a report if you add some helper columns in the sheet(s) and then have it summed/grouped in the report.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!