I have been trying to do this for years and finally decided I would read all
the help stuff when I started using excel 2007. But to no avail. Maybe it
can't be done but I doublt it. It's really simple.
When I name a formula in a cell and subsequently copy that formula to
another cell to calculate a new set of figures I want the name to go with it.
Instead I have to redefine the name to the new cell each time. The name is
be used throughout the workbook in stationary cells.
--
tmk

If you create a formula such as =a1^2 and call it MySquare, then whenever
you use it in a cell say B3, you put: =MySquare, that will take the value in
A3 and square it. The name of the formula does not change. Perhaps you could
give us an example of what you are trying to do. It is very important that
if you create a formula like this one, =a1^2, you define it when you have b1
selected so that the formula will refer to the cell to the left of the cell
in which you use the formula by name.

Tyro


"tmkeny" wrote in message
...
I have been trying to do this for years and finally decided I would read
all
the help stuff when I started using excel 2007. But to no avail. Maybe
it
can't be done but I doublt it. It's really simple.
When I name a formula in a cell and subsequently copy that formula to
another cell to calculate a new set of figures I want the name to go with
it.
Instead I have to redefine the name to the new cell each time. The name is
be used throughout the workbook in stationary cells.
--
tmk

<<<"It is very important that if you create a formula like this one, =a1^2,
you define it when you have b1 selected so that the formula will refer to
the cell to the left of the cell in which you use the formula by name."


Unless, of course, you use an absolute reference:

=Sheet1!$A$1^2

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Tyro" wrote in message
...
If you create a formula such as =a1^2 and call it MySquare, then whenever
you use it in a cell say B3, you put: =MySquare, that will take the value
in A3 and square it. The name of the formula does not change. Perhaps you
could give us an example of what you are trying to do. It is very
important that if you create a formula like this one, =a1^2, you define it
when you have b1 selected so that the formula will refer to the cell to
the left of the cell in which you use the formula by name.

Tyro


"tmkeny" wrote in message
...
I have been trying to do this for years and finally decided I would read
all
the help stuff when I started using excel 2007. But to no avail. Maybe
it
can't be done but I doublt it. It's really simple.
When I name a formula in a cell and subsequently copy that formula to
another cell to calculate a new set of figures I want the name to go with
it.
Instead I have to redefine the name to the new cell each time. The name
is
be used throughout the workbook in stationary cells.
--
tmk

Thanks to you both as you both have given me the solution I think.
It depnds on the order of things. Let's say Im adding several cells in a row
with the total in the right cell i.e a1+a2=a3 so in a3 the formula is =a1+a2
with a3 selected I name the cell lets say total i.e. =total this name is
used in several different places in the work book in an absoute way. with a
new set of figures in row b we now have b1+b2 = b3. I simply copy the
formula from a3 to b3. and have to redefine the name total to cell b3 using
the name manager editor. Trying Tyro's suggestion, instead of doing the copy
to b3 I said =total and got what I was expecting the absolute answer that was
in a3 not the new b3 total. I long ago tried removing the $ signs from the
name formula but it wouldn't allow me to do it. So I guess it must be the
order I am setting things up. If you could show me the error of my ways I
would most appreciate it.
--
tmk


"RagDyer" wrote:

<<<"It is very important that if you create a formula like this one, =a1^2,
you define it when you have b1 selected so that the formula will refer to
the cell to the left of the cell in which you use the formula by name."


Unless, of course, you use an absolute reference:

=Sheet1!$A$1^2

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Tyro" wrote in message
...
If you create a formula such as =a1^2 and call it MySquare, then whenever
you use it in a cell say B3, you put: =MySquare, that will take the value
in A3 and square it. The name of the formula does not change. Perhaps you
could give us an example of what you are trying to do. It is very
important that if you create a formula like this one, =a1^2, you define it
when you have b1 selected so that the formula will refer to the cell to
the left of the cell in which you use the formula by name.

Tyro


"tmkeny" wrote in message
...
I have been trying to do this for years and finally decided I would read
all
the help stuff when I started using excel 2007. But to no avail. Maybe
it
can't be done but I doublt it. It's really simple.
When I name a formula in a cell and subsequently copy that formula to
another cell to calculate a new set of figures I want the name to go with
it.
Instead I have to redefine the name to the new cell each time. The name
is
be used throughout the workbook in stationary cells.
--
tmk

<<<"a1+a2=a3 so in a3 the formula is =a1+a2 with a3 selected I name the cell
lets say total i.e. =total"
This is *WRONG*!

Click in an *empty, blank, unused* A3.
While A3 is selected, from the menu bar:
<Insert <Name <Define

In the "Names In Workbook" box, type
total

THEN ... *Change* whatever's in the "Refers To" box to this:
=A1+A2
Then <OK

You have now created a *relative* named formula.
This formula will total the 2 cells above *wherever it's entered*.

SO, in A3 enter
=total
and you'll get the sum of A1 and A2.

Enter
=total
in B3, and you'll get the sum of B1 and B2

Enter
=total
in K100, and you'll get the sum of K98 and K99.

AND ... it goes *around*!

Enter
=total
in C2, and you'll get the sum of C1 and C65536.

Enter
=total
in C1, and you'll get the sum of C65535 and C65536.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"tmkeny" wrote in message
...
Thanks to you both as you both have given me the solution I think.
It depnds on the order of things. Let's say Im adding several cells in a
row
with the total in the right cell i.e a1+a2=a3 so in a3 the formula is
=a1+a2
with a3 selected I name the cell lets say total i.e. =total this name is
used in several different places in the work book in an absoute way. with
a
new set of figures in row b we now have b1+b2 = b3. I simply copy the
formula from a3 to b3. and have to redefine the name total to cell b3
using
the name manager editor. Trying Tyro's suggestion, instead of doing the
copy
to b3 I said =total and got what I was expecting the absolute answer that
was
in a3 not the new b3 total. I long ago tried removing the $ signs from
the
name formula but it wouldn't allow me to do it. So I guess it must be the
order I am setting things up. If you could show me the error of my ways I
would most appreciate it.
--
tmk


"RagDyer" wrote:

<<<"It is very important that if you create a formula like this one,
=a1^2,
you define it when you have b1 selected so that the formula will refer
to
the cell to the left of the cell in which you use the formula by
name."


Unless, of course, you use an absolute reference:

=Sheet1!$A$1^2

--
Regards,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"Tyro" wrote in message
...
If you create a formula such as =a1^2 and call it MySquare, then
whenever
you use it in a cell say B3, you put: =MySquare, that will take the
value
in A3 and square it. The name of the formula does not change. Perhaps
you
could give us an example of what you are trying to do. It is very
important that if you create a formula like this one, =a1^2, you
define it
when you have b1 selected so that the formula will refer to the cell
to
the left of the cell in which you use the formula by name.

Tyro


"tmkeny" wrote in message
...
I have been trying to do this for years and finally decided I would
read
all
the help stuff when I started using excel 2007. But to no avail.
Maybe
it
can't be done but I doublt it. It's really simple.
When I name a formula in a cell and subsequently copy that formula to
another cell to calculate a new set of figures I want the name to go
with
it.
Instead I have to redefine the name to the new cell each time. The
name
is
be used throughout the workbook in stationary cells.
--
tmk

Thanks Ragdyer for showing me how to establish a relative name. However I
guess I can't have it both ways. I need to use the new answer each time in
various other cells which the new answer must be absolute to. I guess I'll
just have to redefine the name each time unless you have something else up
your sleeve.
Thanks again
--
tmk


"Ragdyer" wrote:

<<<"a1+a2=a3 so in a3 the formula is =a1+a2 with a3 selected I name the cell
lets say total i.e. =total"
This is *WRONG*!

Click in an *empty, blank, unused* A3.
While A3 is selected, from the menu bar:
<Insert <Name <Define

In the "Names In Workbook" box, type
total

THEN ... *Change* whatever's in the "Refers To" box to this:
=A1+A2
Then <OK

You have now created a *relative* named formula.
This formula will total the 2 cells above *wherever it's entered*.

SO, in A3 enter
=total
and you'll get the sum of A1 and A2.

Enter
=total
in B3, and you'll get the sum of B1 and B2

Enter
=total
in K100, and you'll get the sum of K98 and K99.

AND ... it goes *around*!

Enter
=total
in C2, and you'll get the sum of C1 and C65536.

Enter
=total
in C1, and you'll get the sum of C65535 and C65536.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"tmkeny" wrote in message
...
Thanks to you both as you both have given me the solution I think.
It depnds on the order of things. Let's say Im adding several cells in a
row
with the total in the right cell i.e a1+a2=a3 so in a3 the formula is
=a1+a2
with a3 selected I name the cell lets say total i.e. =total this name is
used in several different places in the work book in an absoute way. with
a
new set of figures in row b we now have b1+b2 = b3. I simply copy the
formula from a3 to b3. and have to redefine the name total to cell b3
using
the name manager editor. Trying Tyro's suggestion, instead of doing the
copy
to b3 I said =total and got what I was expecting the absolute answer that
was
in a3 not the new b3 total. I long ago tried removing the $ signs from
the
name formula but it wouldn't allow me to do it. So I guess it must be the
order I am setting things up. If you could show me the error of my ways I
would most appreciate it.
--
tmk


"RagDyer" wrote:

<<<"It is very important that if you create a formula like this one,
=a1^2,
you define it when you have b1 selected so that the formula will refer
to
the cell to the left of the cell in which you use the formula by
name."


Unless, of course, you use an absolute reference:

=Sheet1!$A$1^2

--
Regards,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"Tyro" wrote in message
...
If you create a formula such as =a1^2 and call it MySquare, then
whenever
you use it in a cell say B3, you put: =MySquare, that will take the
value
in A3 and square it. The name of the formula does not change. Perhaps
you
could give us an example of what you are trying to do. It is very
important that if you create a formula like this one, =a1^2, you
define it
when you have b1 selected so that the formula will refer to the cell
to
the left of the cell in which you use the formula by name.

Tyro


"tmkeny" wrote in message
...
I have been trying to do this for years and finally decided I would
read
all
the help stuff when I started using excel 2007. But to no avail.
Maybe
it
can't be done but I doublt it. It's really simple.
When I name a formula in a cell and subsequently copy that formula to
another cell to calculate a new set of figures I want the name to go
with
it.
Instead I have to redefine the name to the new cell each time. The
name
is
be used throughout the workbook in stationary cells.
--
tmk