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

+1 vote

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?

Jan 15 in Others by Kithuzzz
• 27,740 points
31 views

1 answer to this question.

0 votes
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.
answered Jan 15 by narikkadan
• 51,240 points

Related Questions In Others

+1 vote
1 answer
0 votes
1 answer

How to access entire row by cell address or value in excel?

You may accomplish all of this by ...READ MORE

answered Dec 28, 2022 in Others by narikkadan
• 51,240 points
59 views
0 votes
1 answer
0 votes
1 answer

How to split text values by a delimiter?

The Split function is what you are looking for: =Split(A1, ...READ MORE

answered Oct 11, 2022 in Others by narikkadan
• 51,240 points
144 views
0 votes
1 answer

Currency conversion:number to words excel

Try looking for javascript solutions to use ...READ MORE

answered Oct 28, 2022 in Others by narikkadan
• 51,240 points
85 views
+1 vote
1 answer

Excel or Google formula to count occurrences of an 8-digit number within a text string

To match an eight-digit number, you may ...READ MORE

answered Dec 24, 2022 in Others by narikkadan
• 51,240 points
58 views
0 votes
1 answer

Filter/Extract a text from excel sheet having similar values

ISNUMBER(SEARCH("Maria,",SUBSTITUTE(A1:A4,"]",",")))  shows TRUE if Maria is found and false if it would ...READ MORE

answered Jan 9 in Others by narikkadan
• 51,240 points
26 views
0 votes
1 answer

When a row is added to one sheet, automatically add a row to another sheet

Use the VLOOKUP function. Your Spreadsheet 2's first column will ...READ MORE

answered Dec 10, 2022 in Others by narikkadan
• 51,240 points
110 views
0 votes
1 answer

Excel Formula which places date/time in cell when data is entered in another cell in the same row

Here's how to accomplish things in another ...READ MORE

answered Dec 11, 2022 in Others by narikkadan
• 51,240 points
63 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP