Re: name range from absolute to relative

Not sure I understand your problem: what you describe you want sounds to me
like standard Excel behaviour anyway.

If you want a formula to refer to an absolute column (always column C
regardless of how many columns get inserted before C), then use a function
like INDIRECT or OFFSET or INDEX (INDEX is non-volatile but the other 2 are
volatile).

If you want a formula to always refer to the same data/result even when that
data/result gets moved by column/cell insertions then just use standard
referencing, Excel will change the formulae to handle the movement.

If you want to use Relative Names then its best to switch to R1C1 notation
because a relative reference in R1C1 notation DOES NOT CHANGE when you
change the active cell, so its easier to see and understand what you and
Excel are doing. Of course you get exactly the same results with r1c1 as
with a1, its just easier to understand the refersto formula because it
directly shows you the relative offsets ( R[-1]C[-2] refers to the cell one
row above and two columns to the left etc). We added the R1C1/A1 toggle to
the Name manager EUI (bottom right options) to make it easier to flip
between the 2 display modes precisely because of this.

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

"J M Bishop" <JMBishop@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:22F96642-B6ED-411D-8207-A2C000FF38F0@xxxxxxxxxxxxxxxx

Thanks for your reply, and for the Name Manager.

I can see it will be useful. So far it has shown me that the "random"
behavior is not that random, and is related to your comment:

Relative names are tricky because there is no fixed location for the
refersto, so as you change the location of the active cell/*** the
refersto formula will appear to change: you have to keep working out what
the refersto formula is relative to.

Obviously I don't quite understand that yet - how can that possibly be
useful? This behaviour is confirmed by updating the Name Manager window
after moving the cursor to a different cell. ALL of the relative
references
change for ALL of the names when I click on a differents cell.

What I am looking for is a way to define the names with "relative"
references. (Until now I thought I was using the right term.) In this
name
definition, I could name cells using a relative description, and then
insert
columns in between previously named columns and still have results
calculated
based on the previously referenced cells. I would like to be able to do
something like:

- name column A "NameA"
- name column B "NameB"
- define cells in column C to be "=NameA+NameB"
- insert a new column between columns A and B
- have cells in (now) column D to be "=NameA+NameB", with the same result
as
before

I don't think R1C1 mode is going to help me in this case.

Is there another way to do this?

John





"Charles Williams" wrote:

Relative names are tricky because there is no fixed location for the
refersto, so as you change the location of the active cell/*** the
refersto formula will appear to change: you have to keep working out what
the refersto formula is relative to.

Relative names are easier to handle and understand if you switch to R1C1
mode because then the refersto does not change, but I really would
recommend
you download and install Name Manager: it does not cost you anything and
makes all aspects of handling and managing names much easier.

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

"J M Bishop" <JMBishop@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8E972466-D8F3-4D54-9055-5FA07207F560@xxxxxxxxxxxxxxxx

Thanks Charles and Gord:

Gord:
I believe I am in edit mode. (Is there something I'm missing?)
I tried using F4 (and deleted the "$"s directly) which does change the
reference type, and all seems well when I close the window, however if
I
access the name again to verify it, it has randomly changed the range
of
cells I have specified - a different range each time I view it, even
without
changing it. It seems fine if the reference stays absolute, but takes
on
a
life of its own when I change it to relative. The target range is in
column
C. When I change the reference to relative, it changed to specify
column
G.
When I closed the window (without changing it), and viewed the name
description again, it changed to specify column H.

Charles:
To answer your question, here's some background - maybe there's a
better
way
to do it anyway.

I have a spread*** with related data, however it is broken into
several
ranges of rows (say 10-19, 30-39, and 50-59), where I have an arbitrary
number of named columns within those rows (say ALPHA10 refers to
A10-A19,
BETA10 refers to B10-B19). As new parameters need to be added (say
inserting
a column between A and B) to rows 10-19 ONLY (thereby shifting columns
B
and
higher to the right, in rows 10-19 ONLY), I would like to leave the
existing
formulae unchanged, (using the names ALPHA10 in column A and BETA10 now
in
column C,) so that they use the data in the existing columns (within
the
range of rows).

In order to do this, the formulae in the shifted columns need to use
relative references, thereby accessing the same data as was done before
the
new columns were inserted. If the reference is absolute, the formulae
in
the
shifted columns incorrectly refer to the data in the columns to the
left.

(As might be expected, the reason I am naming the cells is to make the
formulae more understandable and less error prone.)

John


"Gord Dibben" wrote:

How are you removing the $ signs?

Are you in Edit mode while removing?

Did you use the F4 to cycle through the options per my second post?


Gord

On Tue, 12 Dec 2006 14:26:01 -0800, J M Bishop <J M
Bishop@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:


To remove the dollar signs would seem to be the logical thing to do,
however
I've tried this on two computers and in both cases the new "refers
to"
formula references a random range of cells - different each time I
try.

As an example, if I remove the "$"'s (all four) from:
=Sheet1!$C$11:$C$16
I get:
=Sheet1!IT16:IT21

John



"Gord Dibben" wrote:

And to remove dollar signs you would select the Refers to: box and
hit
F2 to get
into edit mode.

Select the referenced cells and F4 to cycle through the options
untill
you get
to relative reference.


Gord Dibben MS Excel MVP

On Mon, 14 Aug 2006 12:42:01 -0700, David Billigmeier
<dtbill21@xxxxxxxxxxx>
wrote:

Yes, follow these steps:

<Insert><Name><Define...>
Find your named range in the list and take out the dollar signs.









.

Loading