name range from absolute to relative

[Guest]

When you give a cell a name it defaults to absoute reference. Can you change
the reference from absolute to relative.

 

[Guest]

Yes, follow these steps:

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

 

[Gord Dibben]

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

 

[Charles Williams]

The question you have to ask is 'relative to what?'

You may find Name Manager useful, it has a convert relative to absolute and
vice-versa command (and a whole lot more)

see http://www.decisionModels.com/downloads.htm

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

 

[Gord Dibben]

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

 

[Charles Williams]

Relative names are tricky because there is no fixed location for the
refersto, so as you change the location of the active cell/sheet 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

 

[Charles Williams]

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