Multiple Updates to a Single Row

Emily D

Hello,

I am trying to create a "tracker" that show the progress of a shipment that contains multiple steps that are updated as each step is complete.

So essentially the Tracker sheet would pull all the new entry submissions and update submissions from the dump sheet, to one row on the Tracker for each shipment.

There are multiple people updating this tracker sheet. I was hoping to be able to use a form (for ease) and a "dump" sheet that holds all the updates that would then update to the "tracker" sheet. But I am having troubles getting the tracker row to update as each of the update form submissions are entered.

I have tried using data mesh- it pulls the submission for a new shipment, but then doesn't pull the update submissions to the new shipment row on the tracker. I used Add and Copy setting.

I am having troubles with the multiple updates to the same row.

An example of how many update a row with have.

Form submission 1- New- new shipment request

Form submission 2 - Update- packaged date

form submission 3 - Update- Shipped date

Form submission 4 - Update Arrived at requestor

Form submission 5 - Shipment Complete

I have tried Vlookup but doesn't seem to be able to do what I want. Perhaps I don't have the right formula?

I want this to be automated so asking for updates is more monitoring then what is wanted with this sheet.

Any suggestions on how to get the submissions from my dump sheet to the Tracker sheet into one row?

I am open to using a different setup to achieve this also.

Thanks in advance,

Emily

Jeff Reisman

@Emily D

I knew I shouldn't have answered this pre-coffee. 😆

Try a MAX/COLLECT instead:

=MAX(COLLECT({Dump Sheet Packaged Date}, {Dump Sheet Shipping ID}, [Shipping ID]@row))

In my testing, this pulls the latest date from the Packaged Date column where the Shipping ID matches, so if the other Packaged Date cells in the column for the Shipping ID are blank, it will ignore them to get the "latest" date, aka the only date value in that column for that shipping ID.

(I have to admit, I like your approach to meeting your requirements. Very outside the box kind of thinking.)

Jeff Reisman

@Emily D

Are all of these

Form submission 2 - Update- packaged date

form submission 3 - Update- Shipped date

Form submission 4 - Update Arrived at requestor

Form submission 5 - Shipment Complete

different columns in your tracker sheet and in the dump sheet?

And you're using a Shipment ID of some sort that is the same for each entry pertaining to the shipment?

If so, then it's just a matter of using some INDEX/MATCH formulas in the individual columns in the tracker sheet to pull the values from the dump sheet. For example, in your tracker sheet "Packaged Date" column:

=INDEX({Dump Sheet Packaged Date column}, MATCH([Shipment ID]@row, {Dump Sheet Shipment ID column}, 0))

The values in curly brackets are range references that you select when creating your formula, by click on Reference Another Sheet in the little helper box that comes up.

In English, set this column value to the value in the Dump Sheet Package Date field that is on the row in the Dump Sheet that matches the Shipment ID from this row in the Tracker Sheet.

Emily D

@Jeff Reisman

Thanks for your response.

different columns in your tracker sheet and in the dump sheet?

Yes, they are all different columns in the tracker sheet and the dump sheet.

I have am using a shipping Id that is unique to each shipment that is in the spreadsheet.

I have tried an INDEX/MATCH but it only updates off the first row of the dump sheet. So as the updates are added to the dump sheet the information change on the tracker. Can I do something in the formula to keep the data there and add as the updated come in?

I am using DataMesh to add new Shipments to the tracker.

Here are my files:

Here is the Tracker

here is the dump sheet - Each update is entered by a standard form.

The end Goal would be to combine all the info from the same shipment ID into one row.

Jeff Reisman

@Emily D

I knew I shouldn't have answered this pre-coffee. 😆

Try a MAX/COLLECT instead:

=MAX(COLLECT({Dump Sheet Packaged Date}, {Dump Sheet Shipping ID}, [Shipping ID]@row))

In my testing, this pulls the latest date from the Packaged Date column where the Shipping ID matches, so if the other Packaged Date cells in the column for the Shipping ID are blank, it will ignore them to get the "latest" date, aka the only date value in that column for that shipping ID.

(I have to admit, I like your approach to meeting your requirements. Very outside the box kind of thinking.)

Emily D

@Jeff Reisman

Thanks Jeff! That did the trick it seems!

Thanks you so much for your help.

I have also just made up the following formula to transfer over a comments section where the comments are accumulated from multiple form submissions with JOIN/COLLECT formula. I wouldn't have thought of this with out you help!

Very much appreciated. 😁