Automatically move an entire row of reference cell when one cell is moved or manipulated

Question

I've got multiple Excel sheets. The list of colleges is on Sheet 1 (the master sheet). We make mention of those colleges in other sheets. The other sheets now add a new cell in that same location when we add a new row to the master sheet, but they do not shift the complete row. What will therefore be the optimal course of action? by use of certain scripts?

Exact problem description:

[EDIT] Sheet 1: This is main sheet with college name (Cname) and their joining year (Cval).

Cname1	Cval1
Cname2	Cval2
Cname3	Cval3
Cnmae4	Cval4

Sheet 2: This is another sheet which has college names referenced from Sheet 1 (Cname) and one column of any random person name (Dval).

Cname1	Dval1
Cname2	Dval2
Cname3	Dval3
Cnmae4	Dval4

Sheet 1: If in Sheet 1, I add a new row:

Cname1	Cval1
Cname2	Cval2
Cname3	Cval3
Cnmae4	Cval4

Sheet 2: In Sheet 2, I see a new cell in the row, but the other column (person name) remains as it is.

Cname1	Dval1
Cname2	Dval2
	Dval3
Cname3	Dval4
Cnmae4

If we were to move all the cells in the row, if the first cell of the row moves, what will be the best way to achieve this?

Answer 1

You are describing how manually entered data and formula outcomes diverge. There is no all-inclusive way to circumvent the problem. Consider rearranging the tabs in your spreadsheet so that all of the information that belongs together is in one tab.

In order to address your query, you need set up a trigger that tracks row inserts and deletes and, as needed, adds and removes rows across additional tabs. For an example of code, see the autoInsertAndDeleteRows_ script.