Cross sheet Index/Match with row deletion or addition

Norm S.

Apologies if this question has been posed before, however I have not been able to find a solution by browsing through the community (there is also a good chance I'm approaching the problem incorrectly). I have started building a master project sheet that contains several columns with project related information. I would like to pull some of these columns into a second sheet in order to create a production schedule. I have had success in doing this by using Index/Match. For example, in the screenshots below, I have pulled the Base Beer column from the "Sour Project Release Table v4" sheet into the "2021 Brew Production Table v4" sheet using =INDEX({Sour Project Release Table v4 Base Beer}, MATCH([Project Index #]@row, {Sour Project Release Table v4 Project Index}, 0)). In the "2021 Brew Production Table v4" sheet, I have added several columns so that new information can be added (Batch, Brew Date, Transfer Date, Brew Index #). I would like to be able to make changes to the master project sheet "Sour Project Release Table v4" and have the changes reflect in the "2021 Brew Production Table v4" sheet. However, if I need to insert a new row into the "Sour Project Release Table v4" sheet, it will add the appropriate row to the "2021 Brew Production Table v4" sheet but it will not add an additional row to the manually entered columns (Batch, Brew Date, Transfer Date), causing the data to be offset by one row. I am unsure of how to solve this problem, and if anyone has any insight it would be greatly appreciated.

Sheets before row insertion:

Sheets after row insertion:

Thanks in advance,

Norm

Andrée Starå

Hi @Norm S.

I hope you're well and safe!

If you add an auto number column in the Source sheet and then add a so-called helper column in the Destination sheet and manually add the numbers for how many rows you'd need.

You'd then use the INDEX/MATCH on these columns instead to keep the sheets "synced".

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi @Norm S.

I hope you're well and safe!

If you add an auto number column in the Source sheet and then add a so-called helper column in the Destination sheet and manually add the numbers for how many rows you'd need.

You'd then use the INDEX/MATCH on these columns instead to keep the sheets "synced".

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Norm S.

Thank you for the quick and very helpful response Andrée, and hope the same for you! I've implemented your suggestion and after adding rows to the source sheet they successfully transferred to the destination sheet.

One quick follow up question would be concerning the deletion of a row using this method. After deletion, the link between the auto number and the helper number is broken. Would you suggest in this case a manual deletion of the row on the destination sheet?

Again, thank you for the help!

Norm

Norm S.

Sorry, forgot to tag you in the last comment @Andrée Starå