Is it possible to insert/delete rows in a file and make other files automatically update their rows accordingly?

[Ian Y]

Below are two worksheets. The one named Template.xlsx is used as a template for staves to clone, and it only has four columns. The one named Staff-Tom.xlsx is one of the staff worksheets that was originally cloned using Template.xlsx. Other staves, say Michael, may clone Template.xlsx as well and rename the file as Staff-Michael.xlsx, and start writing content starting from column E.

Column A to column D need to be the same in all worksheets. Regarding column C (Line Count), I mean its formulas need to be the same in all worksheets; the displaying numbers in that column would be conceivably different in all staff worksheets.

My question is that, is it possible to insert or delete rows in Template.xlsx and make all staff worksheets automatically update their rows accordingly? For example, after I delete row 6 (whose ID is g5ofMqYRaI) in Template.xlsx, row 6 in all staff worksheets are automatically deleted, regardless whether staves have written lines in that row; and after I insert a new row at any position in Template.xlsx, all staff worksheets automatically gain that new row at the same position?

Template.xlsx
	A	B	C	D
1	ID	Category	Line Count	Tag
2	jEgzB7egxf	cat6	0	tag2
3	EiLx7pFANh	cat1	0	tag8
4	SnTVQD4gwQ	cat9	0	tag1
5	g7GHnd0jQ4	cat6	0	tag7
6	g5ofMqYRaI	cat3	0	tag10
7	wpDX0biIMR	cat10	0	tag5
8	LJ2YAvty8v	cat2	0	tag8
9	R6pS1llU1V	cat7	0	tag3
10	oX1yY6mf5K	cat5	0	tag1
11	tjZfGS16hq	cat10	0	tag6
Sheet1

Cell Formulas
Range	Formula
C2:C11	C2	=SUM(LEN(E2:Z2)-LEN(SUBSTITUTE(E2:Z2,CHAR(10),""))+(E2:Z2<>""))
Press CTRL+SHIFT+ENTER to enter array formulas.

Staff-Tom.xlsx
	A	B	C	D	E	F
1	ID	Category	Line Count	Tag	7-Jan	8-Jan
2	jEgzB7egxf	cat6	3	tag2	This is a line. This is another line. Yet another line.
3	EiLx7pFANh	cat1	3	tag8	This is the only line.	This is a line. This is a wonderful line.
4	SnTVQD4gwQ	cat9	3	tag1		A line here. How are you? Another line here. Hey! The last line here. See you.
5	g7GHnd0jQ4	cat6	3	tag7	This is a line. Hello. This is a line, too.	I'm the only line here.
6	g5ofMqYRaI	cat3	2	tag10		How are you today? I'm fine. Thank you.
7	wpDX0biIMR	cat10	4	tag5	This is the first line. This is the second line. This is the third line. This is the last line.
8	LJ2YAvty8v	cat2	3	tag8	Only one line in this cell.	Hey. What's up? Let's write another line here.
9	R6pS1llU1V	cat7	2	tag3	This cell has lines. Any other cell can be empty.
10	oX1yY6mf5K	cat5	0	tag1
11	tjZfGS16hq	cat10	3	tag6	This is a whaterever line. A cell can have many lines. A cell can also have only one line.
Sheet1

Cell Formulas
Range	Formula
C2:C11	C2	=SUM(LEN(E2:Z2)-LEN(SUBSTITUTE(E2:Z2,CHAR(10),""))+(E2:Z2<>""))
Press CTRL+SHIFT+ENTER to enter array formulas.

The two worksheets above are streamlined versions for demonstrative purposes. The actual worksheets can contain as many rows as possible. And staff worksheets can contain as many staff-written columns, starting from column E, as possible.

 

[jkpieterse]

In VBA anything is possible, but if you are not 100% sure where the copies are stored you are out of luck trying to remove those rows from all copies.
And depending on the number of copies, this may end up being frightfully slow.

 

[Ian Y]

In VBA anything is possible, but if you are not 100% sure where the copies are stored you are out of luck trying to remove those rows from all copies.
And depending on the number of copies, this may end up being frightfully slow.

All the files, including Template.xlsx, will be stored in the same directory.

I think the number of staff worksheets won't be more than 20. A staff worksheet only needs to update when it is opened. It's not necessary that all staff worksheets are updated at the same time when Template.xlsx is updated.

 

[jkpieterse]

If you want this controlled when one of the copies is opened, you have two choices:

1. Create an add-in which every user (!) must have installed, which monitors opening of files, detects if it is one of those copies, opens the template, compares both and aligns the rows
2. Change the template and all copies of it to xlsm files and add code to them that runs on file open to align the rows

Consider the maintenance of these xlsm files; you might need to update the VBA code one day, in umpteen files...

If these files are in a Sharepoint or OneDrive location, I would try to use Power Automate which would take these steps:

1. Read template, get all identifyers (plus any additional information needed for new rows)
2. Run through all files in the folder, then trigger an Office script which checks their identifyer column and deletes any rows which aren't in the template or adds rows wich aren't in the copy.

I'd decide on how often that script needs to run, like once a day.