When you give a cell a name it defaults to absoute reference. Can you change
the reference from absolute to relative.
--
Your time and consideration would be greatly appreciated.

WCO

Yes, follow these steps:

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

--
Regards,
Dave


"WCO" wrote:

When you give a cell a name it defaults to absoute reference. Can you change
the reference from absolute to relative.
--
Your time and consideration would be greatly appreciated.

WCO

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
wrote:

Yes, follow these steps:

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

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
wrote:

Yes, follow these steps:

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

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

"J M Bishop" <J M wrote in message
...

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

wrote:

Yes, follow these steps:

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

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
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
wrote:

Yes, follow these steps:

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

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 spreadsheet 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
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
wrote:

Yes, follow these steps:

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