Remove Rows That Are No Longer In Report

I've done a little bit of research and from what I'm seeing there isn't a way (but I'm hopeful you all have a workaround). So I have sheets that contain multiple unique IDs for tasks (they are separated out by manager since we have so many of them) and if a task is deleted on a project plan because it isn't needed anymore I need to have a way to delete that task from these sheets. I've tried Datamesh from a report that looks at all project plans but if the unique ID isn't matched it just says unmatched and doesn't update a project status field to blank or not found. I've tried to see if there was a way I could do an index/match from the report but no luck on that. Is there any other way I'm not thinking of? Thank you!

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi@Sarah123

    I hope you're well and safe!

    How many sheets?

    You could maybe add a couple of helper columns in each sheet that checks the other sheets and counts the ID, and if it's missing from one, remove it from the others.

    Would that work/help?

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

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

    做我的帖子(s)帮助或回答你什么stion or solve your problem? Please support the Community bymarking 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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Sarah123
    Sarah123 ✭✭✭

    I have over 100 project plan sheets and I have the rows copying into one master sheet when a task is updated or added (which became too big for Smartsheet to handle very quickly) so I separate those rows out by the manager of the projects into manager sheets (there's about 5 of those). But if a task is just deleted from the project plan sheets it's not translated into any of these other sheets to say "hey delete this task". I'm not sure how to have a help column look at all 100 project plan sheets to see if the task is still there other than via a report to make sure all project plans are being looked at.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Does a row report not work in place of a master sheet that receives copied rows?

    thinkspi.com

  • Sarah123
    Sarah123 ✭✭✭

    How do I link up the report to remove rows that are no longer there? I have a row report that i can use to do the datamesh but it doesn't remove rows that it doesn't find.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I mean a row report that looks at each of the individual Project Plans. I'm not sure I follow exactly what you are trying to do here. If you have a report that pulls in all of the project plans, and something is deleted from a project plan, then it should be removed from the report. I guess what I am not understanding is where the DataMesh fits in.

    thinkspi.com

  • Sarah123
    Sarah123 ✭✭✭

    Basically I have the task IDs being copied to another page - Task IDs (since cell links would be too many). I use Datamesh to update the task IDs with just the information that I'm using for reports and charts. However, if the task ID is deleted from the original project plan the task ID isn't being deleted from my Task IDs sheet.

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭
    edited 03/05/23

    Hi@Sarah123, I think I have a decent solution, but just to confirm the issue: what you're trying to do is delete task ID's from your sheet, "Task IDs", if they've been deleted from the originating task sheet. You create the Task ID data using DataMesh, which is pulling from all your project sheets.

    I think this should work:

    Create an additional "Task ID Ref" column in your Task ID sheet. Then duplicate your DataMesh configurations (sorry, this sounds like a lot!), except just add the Task ID to the Task ID Ref column as a link--you'll need to use the original Task ID as your key ref.

    image.png


    As long as the task ID remains in the project sheet, the link will display the Task ID. When the task is deleted from the project sheet, the link will go blank.

    Set up a time-based automation that is triggered by the Task ID Ref column. Something like: when the cell is blank, move the row to an archive sheet. It will effectively be deleted.

    Let me know if this is a workable solution!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Sarah123I understand your setup. I just don't understand why you are creating a sheet when you already have the report. Is there something specific you are doing with the sheet that the report can't do?

    thinkspi.com

  • Sarah123
    Sarah123 ✭✭✭

    @Lucas Rayalathat could work! Let me see if that causes too many cell links - if it doesn't I think that would work.@Paul Newcomethe main reason of why we are setting it up this way is because it pulls information from multiple sheets together using the task ID.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Sarah123Right. That's what the report does. What I am wondering is why you are trying to convert the master report into a sheet in the first place. Is there something you need done with the data that a report cannot do?

    thinkspi.com

  • Sarah123
    Sarah123 ✭✭✭

    Yes, one of the things we do is have formulas that do calculations on those sheets - such as look at all the project plans and calculate how many hours a resource is working within a week. Unfortunately I don't know of a way to do this from just using reports.

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭
    edited 03/06/23

    @Sarah123,我认为你不应该有一个裁判问题erences. I believe DataMesh creates 1-to-1 links, instead of doing a column lookup. The difference is that when you do an INDEX/COLLECT or /MATCH, the formula has to look in every cell of the referenced page. The number of cells referenced is exponential. For instance, an index/collect column formula in a sheet with 1000 rows, looking at a sheet with 2000 rows, has 1000x2000 or 2,000,000 cell references, which is why INDEX/MATCH runs into a problem. However, if you just do a 1-to-1 match, which I believe DataMesh does using CELL IDs, then a 1000 row column only has 1000 references. We don't have access to CELL IDs accept using API (that's a lot of data to manage). It's kind of an intriguing idea to have an option such that, once a column formula matches a single cell, you could then just flip a switch and say "always refer to this cell" instead of wasting the resources to do continuous lookups. I think I'll add that as a product enhancement request.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Sarah123It MAY be possible using a report. Are you able to share a screenshot of a sample project plan with mock data entered?

    thinkspi.com

  • Sarah123
    Sarah123 ✭✭✭

    @Paul NewcomeI apologize for the delay. The issue with a report is that we look to create conditional formats within the sheet so that when users are looking at the data they can easily see what they need to. The conditional formats are done from calculations on the sheet. I'd love to be able to use a report (since this data is always updated and no need to worry about data mesh or data shuttle not working) but unfortunately I haven't seen the ability to create conditional formatting based on summaries. I'll have to create a mockup of our data to show you what we are working with to see if there's a better way I'm not thinking of (or aware of).

@Steve_Mitchell<\/a>, thank you! I just filed a ticket. i appreciate your help.<\/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":[{"tagID":207,"urlcode":"Sales","name":"Sales"},{"tagID":281,"urlcode":"Support","name":"Support"},{"tagID":446,"urlcode":"update-requests","name":"Update Requests"}]},{"discussionID":106894,"type":"question","name":"How to Count a value in a drop down \/ multi-selection list?","excerpt":"Hi, I'm wanting to count the total amount time this value \"VAVE\" is being assigned to a drop down column. I'm using a summary sheet to collect this data using formulas. So in the request type column(drop down list) I want the total in this sheet. I also want a total by the Brand. I keep getting an incorrect number, like 48…","categoryID":321,"dateInserted":"2023-06-26T15:09:07+00:00","dateUpdated":"2023-06-26T15:09:27+00:00","dateLastComment":"2023-06-26T20:54:24+00:00","insertUserID":162246,"insertUser":{"userID":162246,"name":"Shawn_K2","url":"https:\/\/community.smartsheet.com\/profile\/Shawn_K2","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!nPFlXyflhuI!uj8vZ9JIm_o!1axowkoO9t8","dateLastActive":"2023-06-26T20:56:17+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":162246,"lastUserID":116407,"lastUser":{"userID":116407,"name":"Darren Mullen","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Darren%20Mullen","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/9WIC1TB7AJQT\/nP1GU5H4ITA1P.png","dateLastActive":"2023-06-27T04:13:24+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":53,"score":null,"hot":3375609211,"url":"https:\/\/community.smartsheet.com\/discussion\/106894\/how-to-count-a-value-in-a-drop-down-multi-selection-list","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106894\/how-to-count-a-value-in-a-drop-down-multi-selection-list","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106894,"commentID":382446,"name":"Re: How to Count a value in a drop down \/ multi-selection list?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382446#Comment_382446","dateInserted":"2023-06-26T20:54:24+00:00","insertUserID":116407,"insertUser":{"userID":116407,"name":"Darren Mullen","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Darren%20Mullen","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/9WIC1TB7AJQT\/nP1GU5H4ITA1P.png","dateLastActive":"2023-06-27T04:13:24+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Using Smartsheet","url":"https:\/\/community.smartsheet.com\/categories\/using-smartsheet"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/LKT88AUTSMAT\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-26T16:54:33+00:00","dateAnswered":"2023-06-26T16:44:18+00:00","acceptedAnswers":[{"commentID":382355,"body":"

@Shawn_K2<\/a> UGH I make a mistake. I copied your formula without looking at it close enough 😜<\/span><\/p>


<\/p>

try =COUNTIFS({CPR Request Type}, HAS(@cell, \"VAVE\"))<\/p>"},{"commentID":382446,"body":"

@Shawn_K2<\/a> <\/p>

=COUNTIFS({CPR Request Type}, HAS(@cell, \"VAVE\"), {CPR Brand}, HAS(@cell, \"ING\"))<\/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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":106847,"type":"question","name":"Missing drop-down options in my filter selections. Where did they go?","excerpt":"I have been creating filters and for some reason a couple of my drop-down options are not showing up as filter selections, even though they are correctly showing up as drop-down options in the sheet. To illustrate, here are the drop-down options I have for a column in my sheet, which are working correctly: but when I try…","categoryID":321,"dateInserted":"2023-06-23T18:16:31+00:00","dateUpdated":null,"dateLastComment":"2023-06-26T16:54:06+00:00","insertUserID":162342,"insertUser":{"userID":162342,"name":"mgreenwalt","title":"coordinator","url":"https:\/\/community.smartsheet.com\/profile\/mgreenwalt","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-26T17:20:03+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-06-26T23:47:23+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":10,"countViews":64,"score":null,"hot":3375348637,"url":"https:\/\/community.smartsheet.com\/discussion\/106847\/missing-drop-down-options-in-my-filter-selections-where-did-they-go","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106847\/missing-drop-down-options-in-my-filter-selections-where-did-they-go","format":"Rich","lastPost":{"discussionID":106847,"commentID":382360,"name":"Re: Missing drop-down options in my filter selections. Where did they go?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382360#Comment_382360","dateInserted":"2023-06-26T16:54:06+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-06-26T23:47:23+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Using Smartsheet","url":"https:\/\/community.smartsheet.com\/categories\/using-smartsheet"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/2JV6OYUHWHZT\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-26T16:04:32+00:00","dateAnswered":"2023-06-26T15:48:56+00:00","acceptedAnswers":[{"commentID":382315,"body":"

Thanks @topazfae<\/a> for your time and efforts and I'm happy to announce that @Genevieve P.<\/a> solved this challenge 😃<\/span> - <\/p>

\"I can see that your two selections have 135 and 142 characters each. When I cut the character count under 100<\/strong>, they appear as options to filter by.\"<\/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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=341&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":5409,"limit":3},"title":"Trending in Using Smartsheet","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Using Smartsheet