defined name ranges

[Ben]

Hi all,

I notice something in user defined name ranges.
I inherited an Excel file with close to 30 name ranges. When I ran one of
my routines to extract all the names and ranges of the user defined name
ranges, I noticed they were all in absolute format, for example:
Sheet1!$C$70:$C$193. Normally I thought that means the references do not
change.

The reason for my concern was that there are two adjacent ranges separated
by three rows, so as I expand downward from the first range, as in inserting
new rows, the existing rows would continue to move down, but the referenced
range would stay static, and would still stay the same, or so I thought, but
apparently the user defined name ranges also changes as rows were inserted,
thus, the named range stays intact. I was not aware this was possible when
absolute cell reference is used with the $ sign like above? I am perplex.
Please share your thoughts, thanks in advance.

Ben
--

[Tom Ogilvy]

Why are you perplexed. It works exactly the same in Excel formulas.
Absolute and relative are applied when the formula itself is copied. It you
don't want the reference to move, use Indirect

Refersto: =Indirect("Sheet1!$C$70:$C$193")

--
Regards,
Tom Ogilvy


"Ben" <[email protected]> wrote in message
news:[email protected]...
> Hi all,
>
> I notice something in user defined name ranges.
> I inherited an Excel file with close to 30 name ranges. When I ran one
of
> my routines to extract all the names and ranges of the user defined name
> ranges, I noticed they were all in absolute format, for example:
> Sheet1!$C$70:$C$193. Normally I thought that means the references do not
> change.
>
> The reason for my concern was that there are two adjacent ranges separated
> by three rows, so as I expand downward from the first range, as in
inserting
> new rows, the existing rows would continue to move down, but the
referenced
> range would stay static, and would still stay the same, or so I thought,
but
> apparently the user defined name ranges also changes as rows were
inserted,
> thus, the named range stays intact. I was not aware this was possible
when
> absolute cell reference is used with the $ sign like above? I am perplex.
> Please share your thoughts, thanks in advance.
>
> Ben
> --
>

[Jim Rech]

>>Normally I thought that means the references do not change.

Not at all. The 'absolute' only means that the range a name refers to does
not change when the active cell changes. By comparison, a 'relative' name
does change when the active cell changes.

Select A2 and create the name "UpOne" with the definition =A1 (no $s). Then
select another cell, say A10 and press F5 and Goto 'UpOne'. You'll go to
the cell one above the active cell, A9.

--
Jim
"Ben" <[email protected]> wrote in message
news:[email protected]...
> Hi all,
>
> I notice something in user defined name ranges.
> I inherited an Excel file with close to 30 name ranges. When I ran one
> of
> my routines to extract all the names and ranges of the user defined name
> ranges, I noticed they were all in absolute format, for example:
> Sheet1!$C$70:$C$193. Normally I thought that means the references do not
> change.
>
> The reason for my concern was that there are two adjacent ranges separated
> by three rows, so as I expand downward from the first range, as in
> inserting
> new rows, the existing rows would continue to move down, but the
> referenced
> range would stay static, and would still stay the same, or so I thought,
> but
> apparently the user defined name ranges also changes as rows were
> inserted,
> thus, the named range stays intact. I was not aware this was possible
> when
> absolute cell reference is used with the $ sign like above? I am perplex.
> Please share your thoughts, thanks in advance.
>
> Ben
> --
>

[Ben]

I thought that when using absolute the address doesn't change, but in name
ranges the address of the range can change as columns and rows are added
right before the top left corner of the named range. For example $B2: $C4 is
the named range call it TEST, if I inserted two rows before $B$2, the new
TEST range is now $B$4:$C$6, the range moved. But prior to this, I thought
it would still be $B2: $C4

--



"Tom Ogilvy" wrote:

> Why are you perplexed. It works exactly the same in Excel formulas.
> Absolute and relative are applied when the formula itself is copied. It you
> don't want the reference to move, use Indirect
>
> Refersto: =Indirect("Sheet1!$C$70:$C$193")
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Ben" <[email protected]> wrote in message
> news:[email protected]...
> > Hi all,
> >
> > I notice something in user defined name ranges.
> > I inherited an Excel file with close to 30 name ranges. When I ran one
> of
> > my routines to extract all the names and ranges of the user defined name
> > ranges, I noticed they were all in absolute format, for example:
> > Sheet1!$C$70:$C$193. Normally I thought that means the references do not
> > change.
> >
> > The reason for my concern was that there are two adjacent ranges separated
> > by three rows, so as I expand downward from the first range, as in
> inserting
> > new rows, the existing rows would continue to move down, but the
> referenced
> > range would stay static, and would still stay the same, or so I thought,
> but
> > apparently the user defined name ranges also changes as rows were
> inserted,
> > thus, the named range stays intact. I was not aware this was possible
> when
> > absolute cell reference is used with the $ sign like above? I am perplex.
> > Please share your thoughts, thanks in advance.
> >
> > Ben
> > --
> >
>
>
>

[Ben]

I learned something new. Thanks gentlemen.

Ben

--



"Jim Rech" wrote:

> >>Normally I thought that means the references do not change.
>
> Not at all. The 'absolute' only means that the range a name refers to does
> not change when the active cell changes. By comparison, a 'relative' name
> does change when the active cell changes.
>
> Select A2 and create the name "UpOne" with the definition =A1 (no $s). Then
> select another cell, say A10 and press F5 and Goto 'UpOne'. You'll go to
> the cell one above the active cell, A9.
>
> --
> Jim
> "Ben" <[email protected]> wrote in message
> news:[email protected]...
> > Hi all,
> >
> > I notice something in user defined name ranges.
> > I inherited an Excel file with close to 30 name ranges. When I ran one
> > of
> > my routines to extract all the names and ranges of the user defined name
> > ranges, I noticed they were all in absolute format, for example:
> > Sheet1!$C$70:$C$193. Normally I thought that means the references do not
> > change.
> >
> > The reason for my concern was that there are two adjacent ranges separated
> > by three rows, so as I expand downward from the first range, as in
> > inserting
> > new rows, the existing rows would continue to move down, but the
> > referenced
> > range would stay static, and would still stay the same, or so I thought,
> > but
> > apparently the user defined name ranges also changes as rows were
> > inserted,
> > thus, the named range stays intact. I was not aware this was possible
> > when
> > absolute cell reference is used with the $ sign like above? I am perplex.
> > Please share your thoughts, thanks in advance.
> >
> > Ben
> > --
> >
>
>
>

[Tom Ogilvy]

In A1 put in the formula
=SUM($B$2:$C$4)
then select row 2 and 3 and do Insert Row.

Now the fomrula in A1 reads:
=SUM($B$4:$C$6)

Same with defined names. So you thought incorrectly.

--
Regards,
Tom Ogilvy

"Ben" <[email protected]> wrote in message
news:[email protected]...
> I thought that when using absolute the address doesn't change, but in name
> ranges the address of the range can change as columns and rows are added
> right before the top left corner of the named range. For example $B2: $C4
is
> the named range call it TEST, if I inserted two rows before $B$2, the new
> TEST range is now $B$4:$C$6, the range moved. But prior to this, I
thought
> it would still be $B2: $C4
>
> --
>
>
>
> "Tom Ogilvy" wrote:
>
> > Why are you perplexed. It works exactly the same in Excel formulas.
> > Absolute and relative are applied when the formula itself is copied. It
you
> > don't want the reference to move, use Indirect
> >
> > Refersto: =Indirect("Sheet1!$C$70:$C$193")
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Ben" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi all,
> > >
> > > I notice something in user defined name ranges.
> > > I inherited an Excel file with close to 30 name ranges. When I ran
one
> > of
> > > my routines to extract all the names and ranges of the user defined
name
> > > ranges, I noticed they were all in absolute format, for example:
> > > Sheet1!$C$70:$C$193. Normally I thought that means the references do
not
> > > change.
> > >
> > > The reason for my concern was that there are two adjacent ranges
separated
> > > by three rows, so as I expand downward from the first range, as in
> > inserting
> > > new rows, the existing rows would continue to move down, but the
> > referenced
> > > range would stay static, and would still stay the same, or so I
thought,
> > but
> > > apparently the user defined name ranges also changes as rows were
> > inserted,
> > > thus, the named range stays intact. I was not aware this was
possible
> > when
> > > absolute cell reference is used with the $ sign like above? I am
perplex.
> > > Please share your thoughts, thanks in advance.
> > >
> > > Ben
> > > --
> > >
> >
> >
> >