How to combine cell change notification and Data Shuttle

Nic0

Hello Community,

does anyone has an idea to solve the following problem?

I have a data shuttle running every night, pulling information from an excel spreadsheet into a sheet (let's call it sheet 1).

Do to the fact that there is no unique identifier in that Excel sheet, Data Shuttle completely replaces all the entries in that sheet 1 (which is fine).

In another sheet (sheet 2) I'm pulling information from that sheet via INDEX(Match and INDEX(Collect

There is an automation that notifies people if there was a change in one of these cells in sheet 2.

The problem is, that Smartsheet deletes the value and re-add it in that sheet 2 (even though the value itself did not change) which triggers the notify automation every night.

Is there a way to send notifications only if a value was really changed or alternatively to avoid that the cells in sheet 2 are erased and then refilled with the same value?

I attached a screenshot where you can see how the cells containing the cross-sheet formula are erased and refilled every time the data shuttle ran.

Best wishes and thanks in advance

Nico

Joe Goetschel

The best way to fix the workflow is with a unique identifier. In the system that is updating the Excel, can you have it add a row ID or transaction ID etc?

I thought even maybe doing an autonumbering on the sheet level and then leveraging an automation but this wouldn't work if every day it nukes the sheet.

I would assume seeing you are using a project ID in your screenshot, that the data has several rows with different data using the same project ID etc.

Let me know if I can help further!

Nic0

Hey @Joe Goetschel

thank you for your reply.

I also thought about adding a unique ID into the Excel sheet.

This Excel table is updated manually and new rows are added frequently including changing the data in existing rows.

I know this is a SS community and questions about Excel should be discussed at other places in the internet but to explain my problem just the following.

We have for some projects several rows since they contain of different parts with different timelines.

Let's think about an ice cream seller (I'll use this example later again but it's just to make things easier to understand). We have not rows for every single order but for every flavor that was ordered even though they will be invoiced together in the end (since every flavor has it's own specific timeline)

I thought about unique IDs but Excel does not have something like the Autonumber in SS. I also thought about concatenation of "project ID" and "flavor" to make my own unique ID.

Unfortunately we have clients who place an order like "I want to have a cup with 5 different flavors but I'm not sure about which flavor, yet. Please leave it tbd until I know what I want to have". This makes it super difficult to identify information that remains static over the whole time to use it for a unique ID.

I'll think about this again. Copy / paste IDs from a table when generating a new row in Excel is the only thing I found so far but this is not very user friendly and also quite error prone.

Thanks you so much, maybe I'll find a smart way to have static IDs in my Excel table. Then I'll be able to use the merge function of DataShuttle instead of the "destroy and rebuild" function.

Best wishes

Nico

Joe Goetschel

I have to ask! If the Excel is updated manually, why not just use Smartsheet? Can you expand on this? If it is a user issue, users internal and external are free collaborators.

As for Excel:

Your row ID can be as simple as row 1. row 2, row 3.

Excel auto-numbering:

In the first cell of the range that you want to number, type =ROW(A1). The ROW function returns the number of the row that you reference. For example, =ROW(A1) returns the number 1. Drag the fill handle across the range that you want to fill.

Nic0

Hi @Joe Goetschel

The Excel spreadsheet is also pulled from PowerBI and with the help of slicers the navigation is very user friendly.

In SmartSheet one would have to add several filters to only look at ongoing projects from a specific project manager for example but it's a fair point to think about migrating the whole thing into SS at some point!

The idea with row numbers is very smart and I also tried that some time ago. The problem is that row numbers are not static and when I'm sorting the Excel, all row numbers change and are not longer the identifier for the project as before.

I'll think about turning the whole thing upside down and change the list to SS with an offload once a day for the downstream analysis in PowerBI.

I think this is the best idea so far. Thanks a lot!

Joe Goetschel

Ok more ideas to think about, since I don't know the whole process and all the connections this may or may not be helpful! (If you want to get on a call to brainstorm let me know! )

1. Current user Dashboards/Reports for Project managers (No need for filters) or make reports needed etc
2. Put your Power BI on dashboard in Smartsheet
3. If you are using Microsoft "Things" to add to your Excel, Power automate can add items to Smartsheet

If I can help let me know, otherwise good luck!!