INDIRECT FUNCTION - I want to freeze reference when inserting rows

[dim]

Hi folks,

I have a named range from Cell A2 to Cell A101 in a workbook.

This is entered in the Define Name - Refers To box as:

= INDIRECT(Sheet1!$A$2:$A$101)

When I insert rows in row 2, my references steadily change to $A$3:$A$102
then $A$4:$A$103 an so on.

I need my references to stay the same when rows are inserted. IM GOING
INSANE WITH THIS!

Please help!!!!

 

[ShaneDevenshire]

Hi Dim,

This is a rather unusual use of INDIRECT but if that's what you want, try
this:

=INDIRECT("Sheet1!$A$1:"&Sheet1!$A$11)

I'm assuming you want the A10 part to become A11, if not
=INDIRECT("Sheet1!$A$1:$A$10")

 

[dim]

Hi Shane,

Thanks. I'll try that ASAP. I do NOT want any part of it to change. I want
$A$2:$A$101 to stay EXACTLY the same regardless of rows inserted into Row 2.

I dont want particularly to use INDIRECT. I dont care what I use, I just
want the references to STAY THE SAME when rows are inserted!

Thanks, I'll see if that works and get back.

 

[dim]

OMG! You did it! :-D Thankyou soooo much. Who would have thought that
quotation marks would make such a difference?....well...obviously you would!
haha. Thanks.