Allow row in separate sheet to automatically update cell info

edited 03/05/24 in Smartsheet Basics

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
    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