Excel - Updating Reference Rows and Columns

Question

Hello,

I am having an issue where I would like a grouping of data that is being modified regularly to also be modified in the several locations it is being referenced.

For example:

1. In Sheet1 I have Data in A2:C120
2. In Sheet2, Sheet3, and Sheet4, I want the same data that is in A2:C120. I use a reference where Sheet1 A2 = Sheet2 A2.
3. In Sheet1 I insert a new row, making the data now A2:C:121
4. In Sheet2, Sheet3, and Sheet4, the data skips over the new row and the references adjust, and I am still left with A2:C120.

Using just a reference of ='Sheet1'!A2 for A2 in Sheet2, the data transfers over fine. However, if I insert a new row in Sheet1 row 3, this row will not be inserted into Sheet2, Sheet3, or Sheet4. Is there any way around this? I had considered using a VLOOKUP function but that also would not automatically update the values unless I go into Sheets 2-4. If this explanation needs to be elaborated on please let me know and I can try my best to further explain. Thank you!

Answer 1

Hi there

Let's say data in Sheet1 looks like this

Then try the following formula in the other sheets

Cell A2=INDEX(Sheet1!A:A,ROW())

Adapt the ranges in the formula according to your real scenario

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Regards

Jeovany

Answer 2

Thanks for your help!

The Index function is a step in the right direction!

I think the biggest issue I have is some headers are broken down within the data for project phases (with merged cells out of the 3 rows as a "break." I think the solution to that because it is different formatting is to just have rows ABC be one phase, DEF be another phase, etc. Trying to insert a row so it will insert into another sheet and keep formatting may prove to be more complex than anticipated.