If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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 - |
#4
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|