Comparing Several Smart Sheets

Brad prifast

I am trying to create a smart sheet for customer collections and compare it to previous weeks of data.

So ideally I can export the data which has customer name, different aging columns like 1-30 past due all the way up to 90 days past due and then right after that would be a notes column to put notes about the interaction with customer.

The problem is week over week it changes, but also some people can stay on the sheet for awhile but you cant keep checking each sheet for the notes on that customer.

How do I get it to pull the data if it finds the same customer marker update the dating columns with financial info and notes?

I would love to build the sheet where I just add that weeks new upload and it keeps it up to date

mapotter.sailpoint

Depending on your contract this would be a good place to use Data shuttle. https://help.smartsheet.com/learning-track/integrations-and-add-ons/data-shuttle

A work around would be two sheets:

Sheet one would be a sheet that you work from week over week.

The second sheet would be a sheet that you bring in new data to each week. This can be done by creating a new sheet with import from excel and move the rows to this sheet.

The way I see it working is you would have a column that identifies the client that is system generated "unique" like a customer id. Each time you pull the report this column of data would exist. When you bring in the new weeks information you would have a column with a formula that checks a box (https://help.smartsheet.com/articles/2476171-create-and-edit-formulas-in-smartsheet) if the client is not on the working sheet. If the box is checked then an automation would move that client to the working sheet.

On the working sheet you would have a formula that is an index match "=INDEX({Column to return}, MATCH("Matching Value", {Column with value to match}, 0))" that pulls back the days past due based on a match with the client id. https://help.smartsheet.com/articles/2482647-cross-sheet-formula-combinations

The comment should be made in the row comments, and that will help to track progress.

I know that is a lot of information but hope it helps.