Overwriting existing data in a primary sheet with updates from "helper" sheet

MTaber

Hi All,

Looking for some ideas on how to overwrite data with updated information between a primary sheet and a helper sheet.

Primary Sheet "PO Entry" is used for recording purchase orders, and delivery dates. There CAN be a partial delivery on a PO, and that impacts the admin fee that gets paid.

In cases where there's a partial delivery, I have a workflow set up to copy the row to my "helper" sheet, which has three extra columns to calculate the new PO balance (what's the total sale value of the remaining items for delivery), remaining balance helper (shows the remaining admin fee due for internal verification), and remaining item helper (shows number of items remaining for delivery).

Where I am incredibly, horribly stuck, is overwriting the PO data in my primary sheet to show the updated balances. PO issue date, number, bid item, and estimated delivery would stay the same; "quantity ordered" value would change to the "remaining item helper" qty, "item amount" would change to "new PO balance" value.

Any thoughts? Is this even possible?

Jason Tarpinian

You could do this with DataMesh, which would allow you to essentially copy/paste values from your helper sheet to your main sheet based on a special lookup value. Without that, the only other way would really be to have helper columns on your main sheet that would essentially formulate as

=IFERROR(INDEX({New PO Balance Helper Sheet Range},MATCH([PO Number]@row,{PO Number Helper Sheet Range},0)),"")

So that would return the "New PO Balance" from the helper sheet onto your main sheet if it found a match, but blanks if there wasn't a match. You could then maybe put condi formatting into your primary cells to say, if helper cell isn't blank, black it out or something?

I think until Smartsheet agrees to implement my "Copy value from one cell to another" automation :) I think that's about as close as you can get.