Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, pleaseVisit the Current Forums.
Master sheets - why sheets and not reports?
新学生,许多项目表的设置,然后want the high level info pulled together onto a master sheet. Videos I've watched say to create a master sheet and link data in from project sheets. Project management webinar I watched has a company that designs/fabricates/installs 10,000 signs per year (complicated electric signs, not just printed). So this company must start and also finish 30 to 40 projects per workday (so 60 to 80 projects total daily). Linking seems clumsy to me, especially with high project volume like the sign company, because it is manual. You would be highlighting cells, going to the master sheet, highlighting cells there then linking. Or removing the projects which would include links and then removing blank rows. I'm assuming it's unlikely all the new data would be on consecutive lines on one sheet so all can be linked in one shot. More likely each sign may have it's own project sheet so you would be going from sheet, to sheet, to sheet to set these up on the master/remove them. Then if you follow something like SS's Project Portfolio Rollup system there are 5 master sheets so you would have links that need to be established/removed in additional locations. For some reason some of those mastersheets are sheets and other reports but I don't understand why.
我的问题tion is why ever use a sheet for the master and not always use a report? When run, a report could automatically find all of the info from hundreds of project sheets with no linking. Also I've seen complaints about links going one way, if I want to update something I have to update it on the project sheet (which then updates master) but I can't update directly on master. However reports are two way, I can update on either the project or master sheet and the other will update. Thanks for any help in advance!
Comments
-
Shaine Greenwood Employee
Hi Bill—you bring up a good point about cell linking in a sheet vs using a report.
Cell linking into a "master sheet" allows you to create formulas on said sheet to perform calculations, if needed.
Using a Report is the preferred method (and typically easier to set up), but you can't perform calculations on data in the Report.
-
Bill, having scanned your post, I think you are 100% right, you should be using a Report, not cell linking.
A very simple trick we implemneted is to add a "Roll up" tick box column as standard across all your project sheets. The Report then simply rolls up rows across all sheets where this field is ticked. Makes it very easy and flexible to include / remove rows from the Report.
-
Hi Spencer -
Can you show an example of the "roll up tick"?
Thanks...Helene
-
Sure ... it really is as simple as it sounds. Below is a screen shot of the top left hand corner of one of our project plans. You see the "Roll Up" column with a couple of ticks in it. We have a separate "Activity Overview" report which uses this tick box as the main field on which to roll up the key elements (rows) from each project into on overview for an entire region or business unit.
You can decide at sheet level what consitiutes a "key element" (which can vary from project to project) simply by ticking the box to add or remove that row from the main report.
I should add that each project sheet must contain a minimal number of standar, mandatory fields that make the reporting meaningful.
Did this answer the question?
-
Boomba ✭
Bill - There is a 5000 rows-per-sheet limitation in Smartsheet, which you might need to think about as you build out your solution.
One thing I do is use Spencer's technique above, but label the column "Active?" This box is checked for all new projects. When a project is complete, I uncheck it.
Then when I run a report, I only run it for lines where "Active?" is TRUE. This limits my reports to only active projects. (And if I want to report on completed projects, that just requires me to test for that field to be FALSE.)
One other tip: Each Smartsheet project sheet I create adheres to the file naming convention "YYYY.MM - projectname". This makes it much easier when trying to select a group of sheets to report on in the Report Builder. All the projects from a single month will be grouped together, and easier to locate in that itty bitty "Where?" list in the Report Builder window.
-
Thanks Spencer!
-
Making a report is easer, but you lose the card view which I love to use to check for resource overloading. When you link several sheets into a master, does the master still retain the Card View ability?
Categories
Thank you so much for your solution!<\/p>
It can work, but more like a compromise way. Editing on a report is less convenient.<\/p>
It would be better for me to set access right to each column.<\/p>
But that can also work for now, thanks a lot.<\/p>
By the way are you a BOT?<\/p>"},{"commentID":383283,"body":"
@流风回雪<\/a> <\/p> Excellent!<\/p> Happy to help!<\/p> I agree. Great idea! That would be a great addition to Smartsheet features.<\/p> Please submit this as a Product Feedback or Idea <\/strong>(If it hasn't been added already)<\/em><\/strong> when you have a moment.<\/strong><\/a><\/p> Haha!, No, I'm not a bot! 🤣<\/span><\/p> ✅Remember! <\/strong>Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up\/Awesome or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":107112,"type":"question","name":"How to we exclude a specific name from a DISTINCT LIST USER formula","excerpt":"I would like to exclude a specific user name from this formula. The formula is working fine, and it is returning a distinct list of user names. However, we need to exclude certain users, that are no longer in the group. For example, how do I say to exclude user \"Joe Black\" from this list? =IFERROR(INDEX(DISTINCT({Trial…","snippet":"I would like to exclude a specific user name from this formula. The formula is working fine, and it is returning a distinct list of user names. However, we need to exclude certain…","categoryID":322,"dateInserted":"2023-06-29T22:30:22+00:00","dateUpdated":"2023-06-29T22:33:31+00:00","dateLastComment":"2023-06-30T13:51:12+00:00","insertUserID":157974,"insertUser":{"userID":157974,"name":"Filippo","url":"https:\/\/community.smartsheet.com\/profile\/Filippo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-30T14:54:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":157974,"lastUserID":157974,"lastUser":{"userID":157974,"name":"Filippo","url":"https:\/\/community.smartsheet.com\/profile\/Filippo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-30T14:54:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":64,"score":null,"hot":3376213894,"url":"https:\/\/community.smartsheet.com\/discussion\/107112\/how-to-we-exclude-a-specific-name-from-a-distinct-list-user-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107112\/how-to-we-exclude-a-specific-name-from-a-distinct-list-user-formula","format":"Rich","lastPost":{"discussionID":107112,"commentID":383323,"name":"Re: How to we exclude a specific name from a DISTINCT LIST USER formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383323#Comment_383323","dateInserted":"2023-06-30T13:51:12+00:00","insertUserID":157974,"insertUser":{"userID":157974,"name":"Filippo","url":"https:\/\/community.smartsheet.com\/profile\/Filippo","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-30T14:54:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-30T13:43:49+00:00","dateAnswered":"2023-06-30T12:01:20+00:00","acceptedAnswers":[{"commentID":383296,"body":" Hi @Filippo<\/a> <\/p> You can use a COLLECT Function<\/a> to filter results, for example:<\/p> =IFERROR(INDEX(DISTINCT(COLLECT(<\/strong>{Trial Lead}, {Trial Lead}, <> \"Joe Black\")<\/strong>), [Unique Row ID]@row, 0), \"//m.santa-greenland.com/community/discussion/comment/\")<\/p> An alternative would be to use a Report and Group by the Trial Lead column, ignoring out the users you don't want by adding them to the filter criteria in the Report.<\/p> Cheers,<\/p> Genevieve<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":107109,"type":"question","name":"Bridge Workflow - Runs with no Errors but doesn't copy data to column. Am I missing modules?","excerpt":"I have a Bridge Workflow that is set up to copy data from a row\/column to the same row\/different column. The workflow runs with all module steps \"SUCCEDED\". However, the end result is not copied in my Smartsheet's target column. Its blank. Am I missing a module or module steps to complete the copy routine? -- Here's a…","snippet":"I have a Bridge Workflow that is set up to copy data from a row\/column to the same row\/different column. The workflow runs with all module steps \"SUCCEDED\". However, the end…","categoryID":343,"dateInserted":"2023-06-29T21:27:54+00:00","dateUpdated":null,"dateLastComment":"2023-06-30T15:01:40+00:00","insertUserID":148855,"insertUser":{"userID":148855,"name":"cabbsman","url":"https:\/\/community.smartsheet.com\/profile\/cabbsman","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!88cq90f5xTs!8sDpd7uL9Ps!kj4X4avpgGN","dateLastActive":"2023-06-30T15:02:28+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":91566,"lastUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-06-30T15:01:12+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":28,"score":null,"hot":3376213174,"url":"https:\/\/community.smartsheet.com\/discussion\/107109\/bridge-workflow-runs-with-no-errors-but-doesnt-copy-data-to-column-am-i-missing-modules","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107109\/bridge-workflow-runs-with-no-errors-but-doesnt-copy-data-to-column-am-i-missing-modules","format":"Rich","tagIDs":[369],"lastPost":{"discussionID":107109,"commentID":383333,"name":"Re: Bridge Workflow - Runs with no Errors but doesn't copy data to column. Am I missing modules?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383333#Comment_383333","dateInserted":"2023-06-30T15:01:40+00:00","insertUserID":91566,"insertUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-06-30T15:01:12+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Using Smartsheet","url":"https:\/\/community.smartsheet.com\/categories\/using-smartsheet"},{"name":"Add Ons and Integrations","url":"https:\/\/community.smartsheet.com\/categories\/apps-and-integrations"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/G9744765U09U\/screenshot-28106-29.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screenshot (106).png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-30T14:52:58+00:00","dateAnswered":"2023-06-30T11:53:00+00:00","acceptedAnswers":[{"commentID":383292,"body":" Hi @cabbsman<\/a> <\/p> It looks like you're using the Update Column module, which would update column properties<\/em> (e.g. what elements appear in a dropdown list to select, or changing it to a Contact type of column). <\/p> Instead, use <\/strong>Update Row<\/strong> (see here)<\/a><\/p>