Making rows constant links when inserting

emissivity

New Member
Joined
Mar 10, 2002
Messages
2
I have data on sheet1 linked to sheet2. I need to keep links constant whenever a blank row needs to be inserted. I've tried $A$4, and I've tried naming the cells. What I need is to keep a link on two rows no matter how many lines are inserted.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
On 2002-03-11 11:17, emissivity wrote:
I have data on sheet1 linked to sheet2. I need to keep links constant whenever a blank row needs to be inserted. I've tried $A$4, and I've tried naming the cells. What I need is to keep a link on two rows no matter how many lines are inserted.

If you want your formula(s) to always refer to a certain cell, for example, B2, then you could use the INDIRECT function.

For example,

=INDIRECT("Sheet1!B2")

Will always refer to cell B2 on Sheet1, no matter where you insert/delete rows. But be careful, because it will also always refer to Sheet1, even if you rename Sheet1 (you will get an error - #REF!).

Does this help?

-Russell
 
Upvote 0
Hi Russell
I had the same problem, so I also used =INDIRECT("Sheet1!B2"). However, when I try to copy the formula down the rows, the value of B2 doesn’t increase.
ie I want =INDIRECT("Sheet1!B2") to change to =INDIRECT("Sheet1!B3"), B4 etc
How do I do this?
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top