Remove Rows That Are No Longer In Report

Sarah123

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!

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

✅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!

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

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

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

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.

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

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.

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

@Sarah123 I 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?

Sarah123

@Lucas Rayala that could work! Let me see if that causes too many cell links - if it doesn't I think that would work. @Paul Newcome the 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

@Sarah123 Right. 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?

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

@Sarah123, I don't think you should have a problem with references. 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

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

Sarah123

@Paul Newcome I 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). 😀