Stopping reference following when rows are inserted.

[dim]

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.

 

[David Biddulph]

Use the INDIRECT function.

 

[Susan]

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

Use the INDIRECT function.
--
David Biddulph

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 -

 

[dim]

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.