Formatting A Sheet

Hi all!

I am back again with another Smartsheet question! This time I'm not certain if what I'm asking for is even possible. I was wondering if it would be possible to format 3 columns to make them smaller and more digestible. At the moment, I have a sheet that displays dates tasks have been completed, the week of that date, the days it took to complete the task, as well as the count (how many tasks were completed in that week) and the average tasks completed over that week. Currently the values for week completed, average, and count are displayed all the way down the sheet for 12 weeks worth of tasks. What I would like is for the weeks to be displayed only once, with their respective average, and count next to each other. This is not very comprehensive, don't worry I'll include screen shots as well.

Please find below the screenshots of what I have against what I am aiming for with mock data:

This is what my sheet currently looks likes

I would like the Output Columns to look like this (you can ignore target, its output is constant)

This would make things much easier and more digestible for the user, rather than scrolling constantly through the entire page. The formulas I am using for the Average, Week of, and count are the following:

AVG:

=AVG(COLLECT([Days to Complete]:[Days to Complete], [Week Of]:[Week Of], @cell = [Week Of]@row))

Week Of:

=IF((IF(WEEKDAY([Date Completed]@row) = 6, WEEKNUMBER([Date Completed]@row) + 1, WEEKNUMBER([Date Completed]@row))) > 19, DATE(2020, 12, 30) + ((IF(WEEKDAY([Date Completed]@row) = 6, WEEKNUMBER([Date Completed]@row) + 1, WEEKNUMBER([Date Completed]@row))) * 7 - 4), DATE(2022, 1, 4) + (((IF(WEEKDAY([Date Completed]@row) = 6, WEEKNUMBER([Date Completed]@row) + 1, WEEKNUMBER([Date Completed]@row))) - 1) * 7 - 4))

Count:

=COUNTIF([Week Of]:[Week Of], @cell = [Week Of]@row)

Thank you all in advance, you guys have helped me a great deal on this platform, and have been so nice in your reception of my questions, I really appreciate all of you!

Find more posts tagged with

Sheets

Formulas

functionality

Automations

Conditional Formatting

Accepted answers

Matt Johnson

@Muhammad

Generating a report is the best way to achieve what you are looking for. Currently there is not a feature that I'm aware of that allows grouping in sheets. However, I consider a sheet to be a database of information that is almost never edited itself. I know that might seem crazy to some, but creating reports (that automatically talk back and forth to the sheet) should be the standard when assigning work. Plus reports can pull from multiple sheets so a persons one-stop-shop to-do list can be a single report. And that report can be filtered for things like show me all tasks assigned to me, that are due in the next 5 days, and are not complete. I know that's a little off topic here but I wanted to provide some context to the power of the report tool.

All comments

Matt Johnson

Hi @Muhammad

You should try a report, grouped by Week of, and summarized by Average Days to complete and Count on the other metric. Creating a new report is pretty straight forward, and the group and summarize features seem like a great fit for your situation.

I hope that helps.

Thanks,

Matt

Muhammad

@Matt Johnson Do these features not exist, or can they not be simulated using sheets? Is generating a report the only means by which I can achieve my desired output? Also thank you very much for the help, I did not know reports could be used in such a way!

Matt Johnson

@Muhammad

Muhammad

@Matt Johnson Thank you! I will take your input into consideration! On another note, it is VERY unfortunate that reports themselves cannot be referenced, I have a related project where I want to reference live data AFTER it has been sorted, and I have a report on that data that has all the data I need perfectly organized. Hopefully in the future they make this possible!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.