Making rows constant links when inserting

emissivity · Mar 11th, 2002, 12:17 PM

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.

Russell Hauf · Mar 11th, 2002, 01:58 PM

Quote:

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

emissivity · Mar 14th, 2002, 11:13 AM

Thank-you very much, this is exactly what I was trying to do!

chatty56 · Jul 25th, 2009, 04:45 AM

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?

Jul 25th, 2009, 04:45 AM	#4
chatty56 Board Regular Join Date: Jun 2009 Location: Sydney, ex Johannesburg Posts: 54	Re: Making rows constant links when inserting 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?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 11th, 2002, 12:17 PM	#1
emissivity New Member Join Date: Mar 2002 Posts: 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.

Mar 14th, 2002, 11:13 AM	#3
emissivity New Member Join Date: Mar 2002 Posts: 2	Thank-you very much, this is exactly what I was trying to do!