How to update info from multiple sheets to one master log?

lcain

Good evening. I am working on setting up Smartsheet to track engineering information for my company. Each branch has it's own sheet and one person responsible for logging data into this sheet. That data is then copied to a "Master" sheet for company-wide comparison.

Most of the information is known at the time of input, but some has to be updated later. I have automation set up to copy new rows to the Master, but I do not have a way to copy the updates to the master log other than manually finding each blank space and referencing the correct cell on the correct sheet to get that information once it updates.

Is there a simpler way to update this information?

I have attached a screenshot. Job number and name is unique and will be known at the time the data is initially input and copied to the master, but other information may not be known right away as seen by the blanks.

marc4

If Job Number is unique across all the sheets Data Mesh could use that as the lookup key and then update the master log. One Data Mesh for each of the branches.

/marc

Hollie Green

You can do an automation to automatically copy rows that are changed or added from the original sheet.

There may be a simpler way other than doing it the way I'm suggesting.

I would create 3 helper columns on your master sheet

Row ID - This I would set as an auto numbering column

Duplicate - This I would use a countif formula to count the number of the specific unique identify

=COUNTIF([Job Number]:[Job Number], [Job Number]@row)

Move - This I would a nested if statement to get a 1 or 0

=IF(Duplicate@row > 1, IF([Row ID]@row = MIN([Row ID]:[Row ID]), 1, 0))

You will then need to create what I call a trash sheet (I also use it in case something goes wrong I have the data available) to move the rows to with missing information.

Set up an Automation to Move rows to the new sheet created when the Move column is changed to 1.

The above formulas will ensure the first submitted will be the on that is moved.

lcain

https://community.smartsheet.com/discussion/comment/373467#Comment_373467

This is so close to working! Not sure what the issue is, but the "Move" column is not changing to "1" like it should.

I set everything up like you suggested and everything is working as needed except this column. I have attached a screenshot for reference.

Unfortunately, my company will be phasing Smartsheet out within the next year or two so they do not want to invest in the higher level membership and DataMesh.

Hollie Green

I didn't think about it only gathering the Minimum row number and not gathering the Minimum row number that was part of a duplicate. The below should correct it.

=IF(Duplicate@row > 1, IF(MIN(COLLECT([Row ID]:[Row ID], Duplicate:Duplicate, >1)) = [Row ID]@row, 1, 0), 0)

**Note it will only change 1 row to a 1 at a time. Until that row moves any other duplicates will not change to a 1.