Stopping reference following when rows are inserted.
 

Hi folks,

I have the following cell reference in Book 2:

='C:\Program Files\systems\My Program\Data1\[Book1.xls]Sheet1'!$A$2

I have a macro that automatically enters data in Book1 by inserting rows. I
need the above reference to NOT change to $A$3, $A$4, $A$5 etc as rows are
inserted.

Stopping reference following when rows are inserted.
 

Use the INDIRECT function.
--
David Biddulph

"dim" wrote in message
...
Hi folks,

I have the following cell reference in Book 2:

='C:\Program Files\systems\My Program\Data1\[Book1.xls]Sheet1'!$A$2

I have a macro that automatically enters data in Book1 by inserting rows.
I
need the above reference to NOT change to $A$3, $A$4, $A$5 etc as rows are
inserted.

Stopping reference following when rows are inserted.
 

or name the range in the original workbook & then use the name in your
formula.
name will stay with cell and not move with row or column insertion.

='C:\Program Files\systems\My Program\Data1\[Book1.xls]Sheet1'!MyRange

hth
susan



On Jan 2, 3:04*am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
Use the INDIRECT function.
--
David Biddulph

"dim" wrote in message

...



Hi folks,

I have the following cell reference in Book 2:

='C:\Program Files\systems\My Program\Data1\[Book1.xls]Sheet1'!$A$2

I have a macro that automatically enters data in Book1 by inserting rows..
I
need the above reference to NOT change to $A$3, $A$4, $A$5 etc as rows are
inserted.- Hide quoted text -

- Show quoted text -

Stopping reference following when rows are inserted.
 

Hi again,

I tried susan's way because it seemed the most straightforward, and inserted
a named range from A2 to A101 as "A2toA101".

I incorporated this fine, but when a row was inserted, the named rage
properties changed from =Sheet1!A2:A101 to =Sheet1!A3:A102 !!

I'll try the INDIRECT function if I can figure it out! Thanks.