Allow row in separate sheet to automatically update cell info

ZachN

Hey there,

I have a sheet (Sheet A) where when I add a new row and click a certain checkbox, that row is copied to another sheet (Sheet B). Sometimes, I will need to change the information in the predecessor row from Sheet A, like clicking another checkbox or adding information to a Notes cell. How do I make it so this new information automatically updates the information in Sheet B?

For more context, I want Sheet B to be completely automated; I don't want to have to relink cells every time a new row is added. Also, if I were to use the automation option for copying the row when a row is added and or changed (as opposed to just copying the row when a new row is added), it will duplicate the row with the new information. I'm sure there are a few work arounds with my problem. Is there a good way possibly to automatically check if the identifier column (in my case, an invoice number) in Sheet B matches a cell value, and then to automatically delete the old row and replace it with the new one?

Thanks in advance for any help!

KPH

Instead of copying the row, have you considered just entering the invoice number on sheet B and using an INDEX COLLECT formula in all the other columns to display the information from the corresponding columns in sheet A. If sheet A ever changes, the values in sheet B will also change. You do not need to repeatedly change the references but will need to set up a reference for each column at the start.

If you don't want to enter the invoice number on sheet B you could use the check box method to copy the row from sheet A when checked, and hide all the columns that are copied, except invoice number, then use slightly different column headings to house the columns with the INDEX COLLECT formula. I would prefer to manually enter the invoice number each time to avoid confusion, but this is an option.

Have a look at

https://help.smartsheet.com/function/index

and

https://help.smartsheet.com/function/collect