Unreliable Data Source

ATLAS

Long time listener, first time poster. I have a dataset sheet ("Sales Data") that I only have Viewer access only. The owner of the sheet isn't open to modifying anything so I have to create a solution.

Task: Pull information from this sheet (using 'Index/Collect) into "Timeline Sheet"

Primary question/issue: Rows in the Sale Data sheet are deleted once certain milestones are achieved so using Index/Collect will lead to errors in the Timeline Sheet.

Is there some way I could extract Sales Data rows to Timeline Sheet with a formula without the formula breaking as rows are deleted in Sales Data.

Any help is appreciated!

Paul Newcome

Are you able to provide more details? What exactly is breaking when new rows are deleted? Do you need to manually input additional data after pulling in certain rows? Why exactly are you using INDEX/COLLECT? Is it possible to use a row report? Why do you need to bring this data over to a separate sheet? Is your screenshot the source data or the target sheet?

ATLAS

https://community.smartsheet.com/discussion/comment/428376#Comment_428376

Hi Paul,

The screenshot in the first post is of the sample Sales Data Data Source Revised, where I'm replicating the "root" data source . I've also attached the sample Timeline Sheet below.

The reason I have to transfer data over is because of constraints with the "root" data source sheet that I don't admin. The intent of the Sales Data Data Source Revised sheet is to mirror the "root" source and retain any rows deleted on "root". It also allows me to create helper columns to associate with one other sheet.

Let me know if you have any other questions. Thank you.

Paul Newcome

What kind of scale are we talking about? How many rows would be needed on your end? There MIGHT be a way, but it will be a tedious set-up and won't scale very well if you have a lot of rows to track.

Either that, or you will need to leverage the API.

Andrée Starå

Hi @ATLAS

I hope you're well and safe!

You don't need to keep the data from the source, and only mirror the data. Correct?

The INDEX function and a helper column in the destination sheet could be used.

In that way, row 1 in the destination sheet will always reflect row 1 from the source, and so on.

Make sense?

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!

Paul Newcome

@Andrée Starå

"The intent of the Sales Data Data Source Revised sheet is to mirror the "root" source and retain any rows deleted on "root"."

Andrée Starå

@Paul Newcome

Thanks!

I read it differently! (Coffetime)

ATLAS

https://community.smartsheet.com/discussion/comment/428532#Comment_428532

Right now there are roughly 1,500 rows in the 'root' data source. When new data is entered, it can range anywhere from adding approx 10-100 rows at a time. I was originally thinking I could create a workflow automation to copy new rows in the 'root' source to Data Source Revised but then if any values on the root source change, they would not be reflected on the revised data source. What is the tedious method you're thinking? Thank you Paul.

ATLAS

https://community.smartsheet.com/discussion/comment/428541#Comment_428541

Thank you for your response, Andrée. Unfortunately rows are deleted on the root data source so INDEX wouldn't work in this situation. Have a great day!

Paul Newcome

@ATLAS It involves a combination of the INDEX function as well as a copy or move row automation. At this point it is purely conceptual. I haven't actually built or tested anything yet, but honestly the more I think through it, the less confident I become that it would actually work.

How frequently do things change that you would need to capture, and is there definitely no way to get the other owner to work with you? Even something as simple as instead of deleting a row, moving it to another "archive" sheet?

ATLAS

https://community.smartsheet.com/discussion/comment/428738#Comment_428738

Data changes frequently, which was why I thought INDEX/COLLECT would be perfect. The more I've thought about it and have heard your feedback, the only solution is collaboration (Easiest pitch will probably be moving to archive sheet). Creating an overly complex workaround just isn't efficient. I really appreciate your time Paul.

Andrée Starå

@ATLAS

Happy to help!

Is there a unique value / ID in the root source data that will only be used for that record?

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

Paul Newcome

@ATLAS Collaboration would definitely be your best bet here. You can even give them some basic instructions on how to set it up in just a few minutes. You would create the archive sheet and give them admin permissions. When you share the sheet to them, you can uncheck the box to send them a notification so there is no confusion or worry there.

Then you can simply tell them to insert a checkbox column called "Remove" then set up a basic Move Row automation to move rows when the box becomes checked and give them the archive sheet name.

Then moving forward they would check the Remove box and save the sheet, and the row will automatically be removed from the sheet in just a few minutes.