Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying named formulas
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying named formulas
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying named formulas
<<<"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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying named formulas
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying named formulas
<<<"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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying named formulas
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying named formulas
I don't quite follow exactly what you're asking for (new answer).
Care to elaborate a little, with some examples? -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "tmkeny" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying named formulas
All I mean by that is when I copy the formula or use =name in the cell in the
next row of figures I get a new answer for whatever the formula does that I use in other stationary cells in the workbook. i.e. a1+a2=a3 b1+b2=b3 etc. a3 and b3 are new answers to the formula calulation. I'm recoding the new result in other cells with =name. To do this the result must be absolute. So I must redefine name each time for the new answer. -- tmk "RagDyeR" wrote: I don't quite follow exactly what you're asking for (new answer). Care to elaborate a little, with some examples? -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "tmkeny" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying named formulas
Can't you define multiple names to designate each of your "new answer"
cells? A3 might be nameA B3 might be nameB Or ... are the answer cells constantly changing? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "tmkeny" wrote in message ... All I mean by that is when I copy the formula or use =name in the cell in the next row of figures I get a new answer for whatever the formula does that I use in other stationary cells in the workbook. i.e. a1+a2=a3 b1+b2=b3 etc. a3 and b3 are new answers to the formula calulation. I'm recoding the new result in other cells with =name. To do this the result must be absolute. So I must redefine name each time for the new answer. -- tmk "RagDyeR" wrote: I don't quite follow exactly what you're asking for (new answer). Care to elaborate a little, with some examples? -- Regards, RD -------------------------------------------------------------------------- --------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------------- --------------------- "tmkeny" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying named formulas
yes the answers constantly change and must be recorded in the cells that are
=name. no I can't define new names each time. That would defeat the purpose. I want the new values to be recorded in the designated cells that say =name. What I have been doing is redifining the name to the new cell. I can't believe that there isn't away to do this but is does involve relative vs absolute. But you did show me how to make relative names if I ever need to do so. Thanks. -- tmk "Ragdyer" wrote: Can't you define multiple names to designate each of your "new answer" cells? A3 might be nameA B3 might be nameB Or ... are the answer cells constantly changing? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "tmkeny" wrote in message ... All I mean by that is when I copy the formula or use =name in the cell in the next row of figures I get a new answer for whatever the formula does that I use in other stationary cells in the workbook. i.e. a1+a2=a3 b1+b2=b3 etc. a3 and b3 are new answers to the formula calulation. I'm recoding the new result in other cells with =name. To do this the result must be absolute. So I must redefine name each time for the new answer. -- tmk "RagDyeR" wrote: I don't quite follow exactly what you're asking for (new answer). Care to elaborate a little, with some examples? -- Regards, RD -------------------------------------------------------------------------- --------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------------- --------------------- "tmkeny" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying named formulas
My question was not about the *answers* changing ... it was whether or not
the *cells* containing the answers change? If the cells themselves don't change, they can be assigned individual names. Maybe you could describe your scenario, elaborating as to what you are trying to accomplish. Including the whole picture, instead of just a single component or segment of your project, can sometimes make a solution obvious, where before, it might not have even been brought into consideration. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "tmkeny" wrote in message ... yes the answers constantly change and must be recorded in the cells that are =name. no I can't define new names each time. That would defeat the purpose. I want the new values to be recorded in the designated cells that say =name. What I have been doing is redifining the name to the new cell. I can't believe that there isn't away to do this but is does involve relative vs absolute. But you did show me how to make relative names if I ever need to do so. Thanks. -- tmk "Ragdyer" wrote: Can't you define multiple names to designate each of your "new answer" cells? A3 might be nameA B3 might be nameB Or ... are the answer cells constantly changing? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "tmkeny" wrote in message ... All I mean by that is when I copy the formula or use =name in the cell in the next row of figures I get a new answer for whatever the formula does that I use in other stationary cells in the workbook. i.e. a1+a2=a3 b1+b2=b3 etc. a3 and b3 are new answers to the formula calulation. I'm recoding the new result in other cells with =name. To do this the result must be absolute. So I must redefine name each time for the new answer. -- tmk "RagDyeR" wrote: I don't quite follow exactly what you're asking for (new answer). Care to elaborate a little, with some examples? -- Regards, RD -------------------------------------------------------------------------- --------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------------- --------------------- "tmkeny" wrote in message ... 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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying named formulas
Every month I apply the formula to a new set of figures in the next row
down. So the named cell has changed as well as the result. The name must remain the same as it is used in other locations throughout the workbook. The previous results in the rows above become irrelavent. If I used a new name I'd have to be changing the =name throughout the workbook. It's quicker to redefine the name to the new cell with the name manager. That's what I was trying to eliminate having to do. But thanks again for your interest and suggestions. tmk "RagDyeR" wrote: My question was not about the *answers* changing ... it was whether or not the *cells* containing the answers change? If the cells themselves don't change, they can be assigned individual names. Maybe you could describe your scenario, elaborating as to what you are trying to accomplish. Including the whole picture, instead of just a single component or segment of your project, can sometimes make a solution obvious, where before, it might not have even been brought into consideration. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "tmkeny" wrote in message ... yes the answers constantly change and must be recorded in the cells that are =name. no I can't define new names each time. That would defeat the purpose. I want the new values to be recorded in the designated cells that say =name. What I have been doing is redifining the name to the new cell. I can't believe that there isn't away to do this but is does involve relative vs absolute. But you did show me how to make relative names if I ever need to do so. Thanks. -- tmk "Ragdyer" wrote: Can't you define multiple names to designate each of your "new answer" cells? A3 might be nameA B3 might be nameB Or ... are the answer cells constantly changing? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "tmkeny" wrote in message ... All I mean by that is when I copy the formula or use =name in the cell in the next row of figures I get a new answer for whatever the formula does that I use in other stationary cells in the workbook. i.e. a1+a2=a3 b1+b2=b3 etc. a3 and b3 are new answers to the formula calulation. I'm recoding the new result in other cells with =name. To do this the result must be absolute. So I must redefine name each time for the new answer. -- tmk "RagDyeR" wrote: I don't quite follow exactly what you're asking for (new answer). Care to elaborate a little, with some examples? -- Regards, RD -------------------------------------------------------------------------- --------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------------- --------------------- "tmkeny" wrote in message ... 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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying named formulas
Now that you've described what you're looking for, I think there's an easy
solution. You say you add figures to the next row down. Let's say that you're referring to Column A, and you want to use the *last* number in Column A for all your *existing* formulas in the sheet, where you've referenced that last cell with a name of "total". i.e. ... =B2/total =Sum(D1:D10,total) This will assign the name total to the *last* number that is in Column A. It can also be a formula that returns a number. <Insert <Name <Define In the "Names In Workbook" box, enter total Then *Change* whatever's in the "Refers To" box to: =LOOKUP(99^99,Sheet1!$A:$A) Then <OK. Now, wherever "total" is used in a formula, it will refer to the *last* number entered in Column A. Text and nulls ( "" ) and logicals (True, False) and errors (#N/A, #VALUE!, ....etc.) will be by-passed. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "tmkeny" wrote in message ... Every month I apply the formula to a new set of figures in the next row down. So the named cell has changed as well as the result. The name must remain the same as it is used in other locations throughout the workbook. The previous results in the rows above become irrelavent. If I used a new name I'd have to be changing the =name throughout the workbook. It's quicker to redefine the name to the new cell with the name manager. That's what I was trying to eliminate having to do. But thanks again for your interest and suggestions. tmk "RagDyeR" wrote: My question was not about the *answers* changing ... it was whether or not the *cells* containing the answers change? If the cells themselves don't change, they can be assigned individual names. Maybe you could describe your scenario, elaborating as to what you are trying to accomplish. Including the whole picture, instead of just a single component or segment of your project, can sometimes make a solution obvious, where before, it might not have even been brought into consideration. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "tmkeny" wrote in message ... yes the answers constantly change and must be recorded in the cells that are =name. no I can't define new names each time. That would defeat the purpose. I want the new values to be recorded in the designated cells that say =name. What I have been doing is redifining the name to the new cell. I can't believe that there isn't away to do this but is does involve relative vs absolute. But you did show me how to make relative names if I ever need to do so. Thanks. -- tmk "Ragdyer" wrote: Can't you define multiple names to designate each of your "new answer" cells? A3 might be nameA B3 might be nameB Or ... are the answer cells constantly changing? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "tmkeny" wrote in message ... All I mean by that is when I copy the formula or use =name in the cell in the next row of figures I get a new answer for whatever the formula does that I use in other stationary cells in the workbook. i.e. a1+a2=a3 b1+b2=b3 etc. a3 and b3 are new answers to the formula calulation. I'm recoding the new result in other cells with =name. To do this the result must be absolute. So I must redefine name each time for the new answer. -- tmk "RagDyeR" wrote: I don't quite follow exactly what you're asking for (new answer). Care to elaborate a little, with some examples? -- Regards, RD -------------------------------------------------------------------------- --------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------------- --------------------- "tmkeny" wrote in message ... 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 |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying named formulas
Thanks, though I'm working in rows across columns, what you have suggested
should work. I am not familiar with the lookup function and the 99^99 reference but I'll give it a try. -- tmk "RagDyer" wrote: Now that you've described what you're looking for, I think there's an easy solution. You say you add figures to the next row down. Let's say that you're referring to Column A, and you want to use the *last* number in Column A for all your *existing* formulas in the sheet, where you've referenced that last cell with a name of "total". i.e. ... =B2/total =Sum(D1:D10,total) This will assign the name total to the *last* number that is in Column A. It can also be a formula that returns a number. <Insert <Name <Define In the "Names In Workbook" box, enter total Then *Change* whatever's in the "Refers To" box to: =LOOKUP(99^99,Sheet1!$A:$A) Then <OK. Now, wherever "total" is used in a formula, it will refer to the *last* number entered in Column A. Text and nulls ( "" ) and logicals (True, False) and errors (#N/A, #VALUE!, ....etc.) will be by-passed. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "tmkeny" wrote in message ... Every month I apply the formula to a new set of figures in the next row down. So the named cell has changed as well as the result. The name must remain the same as it is used in other locations throughout the workbook. The previous results in the rows above become irrelavent. If I used a new name I'd have to be changing the =name throughout the workbook. It's quicker to redefine the name to the new cell with the name manager. That's what I was trying to eliminate having to do. But thanks again for your interest and suggestions. tmk "RagDyeR" wrote: My question was not about the *answers* changing ... it was whether or not the *cells* containing the answers change? If the cells themselves don't change, they can be assigned individual names. Maybe you could describe your scenario, elaborating as to what you are trying to accomplish. Including the whole picture, instead of just a single component or segment of your project, can sometimes make a solution obvious, where before, it might not have even been brought into consideration. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "tmkeny" wrote in message ... yes the answers constantly change and must be recorded in the cells that are =name. no I can't define new names each time. That would defeat the purpose. I want the new values to be recorded in the designated cells that say =name. What I have been doing is redifining the name to the new cell. I can't believe that there isn't away to do this but is does involve relative vs absolute. But you did show me how to make relative names if I ever need to do so. Thanks. -- tmk "Ragdyer" wrote: Can't you define multiple names to designate each of your "new answer" cells? A3 might be nameA B3 might be nameB Or ... are the answer cells constantly changing? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "tmkeny" wrote in message ... All I mean by that is when I copy the formula or use =name in the cell in the next row of figures I get a new answer for whatever the formula does that I use in other stationary cells in the workbook. i.e. a1+a2=a3 b1+b2=b3 etc. a3 and b3 are new answers to the formula calulation. I'm recoding the new result in other cells with =name. To do this the result must be absolute. So I must redefine name each time for the new answer. -- tmk "RagDyeR" wrote: I don't quite follow exactly what you're asking for (new answer). Care to elaborate a little, with some examples? -- Regards, RD -------------------------------------------------------------------------- --------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------------- --------------------- "tmkeny" wrote in message ... 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." |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying named formulas
You're right, it'll work just as well in rows.
=LOOKUP(99^99,Sheet1!$1:$1) Of course, you really don't need to assign the name to an entire row. You could just as well use something like this: =LOOKUP(99^99,Sheet1!$A$1:$J$1) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "tmkeny" wrote in message ... Thanks, though I'm working in rows across columns, what you have suggested should work. I am not familiar with the lookup function and the 99^99 reference but I'll give it a try. -- tmk "RagDyer" wrote: Now that you've described what you're looking for, I think there's an easy solution. You say you add figures to the next row down. Let's say that you're referring to Column A, and you want to use the *last* number in Column A for all your *existing* formulas in the sheet, where you've referenced that last cell with a name of "total". i.e. ... =B2/total =Sum(D1:D10,total) This will assign the name total to the *last* number that is in Column A. It can also be a formula that returns a number. <Insert <Name <Define In the "Names In Workbook" box, enter total Then *Change* whatever's in the "Refers To" box to: =LOOKUP(99^99,Sheet1!$A:$A) Then <OK. Now, wherever "total" is used in a formula, it will refer to the *last* number entered in Column A. Text and nulls ( "" ) and logicals (True, False) and errors (#N/A, #VALUE!, ....etc.) will be by-passed. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "tmkeny" wrote in message ... Every month I apply the formula to a new set of figures in the next row down. So the named cell has changed as well as the result. The name must remain the same as it is used in other locations throughout the workbook. The previous results in the rows above become irrelavent. If I used a new name I'd have to be changing the =name throughout the workbook. It's quicker to redefine the name to the new cell with the name manager. That's what I was trying to eliminate having to do. But thanks again for your interest and suggestions. tmk "RagDyeR" wrote: My question was not about the *answers* changing ... it was whether or not the *cells* containing the answers change? If the cells themselves don't change, they can be assigned individual names. Maybe you could describe your scenario, elaborating as to what you are trying to accomplish. Including the whole picture, instead of just a single component or segment of your project, can sometimes make a solution obvious, where before, it might not have even been brought into consideration. -- Regards, RD ------------------------------------------------------------------------- ---------------------- Please keep all correspondence within the Group, so all may benefit ! ------------------------------------------------------------------------- ---------------------- "tmkeny" wrote in message ... yes the answers constantly change and must be recorded in the cells that are =name. no I can't define new names each time. That would defeat the purpose. I want the new values to be recorded in the designated cells that say =name. What I have been doing is redifining the name to the new cell. I can't believe that there isn't away to do this but is does involve relative vs absolute. But you did show me how to make relative names if I ever need to do so. Thanks. -- tmk "Ragdyer" wrote: Can't you define multiple names to designate each of your "new answer" cells? A3 might be nameA B3 might be nameB Or ... are the answer cells constantly changing? -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "tmkeny" wrote in message ... All I mean by that is when I copy the formula or use =name in the cell in the next row of figures I get a new answer for whatever the formula does that I use in other stationary cells in the workbook. i.e. a1+a2=a3 b1+b2=b3 etc. a3 and b3 are new answers to the formula calulation. I'm recoding the new result in other cells with =name. To do this the result must be absolute. So I must redefine name each time for the new answer. -- tmk "RagDyeR" wrote: I don't quite follow exactly what you're asking for (new answer). Care to elaborate a little, with some examples? -- Regards, RD -------------------------------------------------------------------------- --------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------------- --------------------- "tmkeny" wrote in message ... 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." |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying named formulas
Sorry for late reply. That works like a champ and is saving me a lot of
unnecessary steps. I guess I should get familiar with functions and macros. Thanks again and signing off. By the way I realized I was giving you bad examples as to what I was doing which led you to believe I was working in columns instead of rows. -- tmk "Ragdyer" wrote: You're right, it'll work just as well in rows. =LOOKUP(99^99,Sheet1!$1:$1) Of course, you really don't need to assign the name to an entire row. You could just as well use something like this: =LOOKUP(99^99,Sheet1!$A$1:$J$1) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "tmkeny" wrote in message ... Thanks, though I'm working in rows across columns, what you have suggested should work. I am not familiar with the lookup function and the 99^99 reference but I'll give it a try. -- tmk "RagDyer" wrote: Now that you've described what you're looking for, I think there's an easy solution. You say you add figures to the next row down. Let's say that you're referring to Column A, and you want to use the *last* number in Column A for all your *existing* formulas in the sheet, where you've referenced that last cell with a name of "total". i.e. ... =B2/total =Sum(D1:D10,total) This will assign the name total to the *last* number that is in Column A. It can also be a formula that returns a number. <Insert <Name <Define In the "Names In Workbook" box, enter total Then *Change* whatever's in the "Refers To" box to: =LOOKUP(99^99,Sheet1!$A:$A) Then <OK. Now, wherever "total" is used in a formula, it will refer to the *last* number entered in Column A. Text and nulls ( "" ) and logicals (True, False) and errors (#N/A, #VALUE!, ....etc.) will be by-passed. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "tmkeny" wrote in message ... Every month I apply the formula to a new set of figures in the next row down. So the named cell has changed as well as the result. The name must remain the same as it is used in other locations throughout the workbook. The previous results in the rows above become irrelavent. If I used a new name I'd have to be changing the =name throughout the workbook. It's quicker to redefine the name to the new cell with the name manager. That's what I was trying to eliminate having to do. But thanks again for your interest and suggestions. tmk "RagDyeR" wrote: My question was not about the *answers* changing ... it was whether or not the *cells* containing the answers change? If the cells themselves don't change, they can be assigned individual names. Maybe you could describe your scenario, elaborating as to what you are trying to accomplish. Including the whole picture, instead of just a single component or segment of your project, can sometimes make a solution obvious, where before, it might not have even been brought into consideration. -- Regards, RD ------------------------------------------------------------------------- ---------------------- Please keep all correspondence within the Group, so all may benefit ! ------------------------------------------------------------------------- ---------------------- "tmkeny" wrote in message ... yes the answers constantly change and must be recorded in the cells that are =name. no I can't define new names each time. That would defeat the purpose. I want the new values to be recorded in the designated cells that say =name. What I have been doing is redifining the name to the new cell. I can't believe that there isn't away to do this but is does involve relative vs absolute. But you did show me how to make relative names if I ever need to do so. Thanks. -- tmk "Ragdyer" wrote: Can't you define multiple names to designate each of your "new answer" cells? A3 might be nameA B3 might be nameB Or ... are the answer cells constantly changing? -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "tmkeny" wrote in message ... All I mean by that is when I copy the formula or use =name in the cell in the next row of figures I get a new answer for whatever the formula does that I use in other stationary cells in the workbook. i.e. a1+a2=a3 b1+b2=b3 etc. a3 and b3 are new answers to the formula calulation. I'm recoding the new result in other cells with =name. To do this the result must be absolute. So I must redefine name each time for the new answer. -- tmk "RagDyeR" wrote: I don't quite follow exactly what you're asking for (new answer). Care to elaborate a little, with some examples? -- Regards, RD -------------------------------------------------------------------------- --------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------------- --------------------- "tmkeny" wrote in message ... 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 -------------------------------------------------------------------------- - |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copying named formulas
Really appreciate the feed-back ... no matter how delayed it might be.
Glad you got it working. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "tmkeny" wrote in message ... Sorry for late reply. That works like a champ and is saving me a lot of unnecessary steps. I guess I should get familiar with functions and macros. Thanks again and signing off. By the way I realized I was giving you bad examples as to what I was doing which led you to believe I was working in columns instead of rows. -- tmk "Ragdyer" wrote: You're right, it'll work just as well in rows. =LOOKUP(99^99,Sheet1!$1:$1) Of course, you really don't need to assign the name to an entire row. You could just as well use something like this: =LOOKUP(99^99,Sheet1!$A$1:$J$1) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "tmkeny" wrote in message ... Thanks, though I'm working in rows across columns, what you have suggested should work. I am not familiar with the lookup function and the 99^99 reference but I'll give it a try. -- tmk "RagDyer" wrote: Now that you've described what you're looking for, I think there's an easy solution. You say you add figures to the next row down. Let's say that you're referring to Column A, and you want to use the *last* number in Column A for all your *existing* formulas in the sheet, where you've referenced that last cell with a name of "total". i.e. ... =B2/total =Sum(D1:D10,total) This will assign the name total to the *last* number that is in Column A. It can also be a formula that returns a number. <Insert <Name <Define In the "Names In Workbook" box, enter total Then *Change* whatever's in the "Refers To" box to: =LOOKUP(99^99,Sheet1!$A:$A) Then <OK. Now, wherever "total" is used in a formula, it will refer to the *last* number entered in Column A. Text and nulls ( "" ) and logicals (True, False) and errors (#N/A, #VALUE!, ....etc.) will be by-passed. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "tmkeny" wrote in message ... Every month I apply the formula to a new set of figures in the next row down. So the named cell has changed as well as the result. The name must remain the same as it is used in other locations throughout the workbook. The previous results in the rows above become irrelavent. If I used a new name I'd have to be changing the =name throughout the workbook. It's quicker to redefine the name to the new cell with the name manager. That's what I was trying to eliminate having to do. But thanks again for your interest and suggestions. tmk "RagDyeR" wrote: My question was not about the *answers* changing ... it was whether or not the *cells* containing the answers change? If the cells themselves don't change, they can be assigned individual names. Maybe you could describe your scenario, elaborating as to what you are trying to accomplish. Including the whole picture, instead of just a single component or segment of your project, can sometimes make a solution obvious, where before, it might not have even been brought into consideration. -- Regards, RD ------------------------------------------------------------------------- ---------------------- Please keep all correspondence within the Group, so all may benefit ! ------------------------------------------------------------------------- ---------------------- "tmkeny" wrote in message ... yes the answers constantly change and must be recorded in the cells that are =name. no I can't define new names each time. That would defeat the purpose. I want the new values to be recorded in the designated cells that say =name. What I have been doing is redifining the name to the new cell. I can't believe that there isn't away to do this but is does involve relative vs absolute. But you did show me how to make relative names if I ever need to do so. Thanks. -- tmk "Ragdyer" wrote: Can't you define multiple names to designate each of your "new answer" cells? A3 might be nameA B3 might be nameB Or ... are the answer cells constantly changing? -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "tmkeny" wrote in message ... All I mean by that is when I copy the formula or use =name in the cell in the next row of figures I get a new answer for whatever the formula does that I use in other stationary cells in the workbook. i.e. a1+a2=a3 b1+b2=b3 etc. a3 and b3 are new answers to the formula calulation. I'm recoding the new result in other cells with =name. To do this the result must be absolute. So I must redefine name each time for the new answer. -- tmk "RagDyeR" wrote: I don't quite follow exactly what you're asking for (new answer). Care to elaborate a little, with some examples? -- Regards, RD -------------------------------------------------------------------------- --------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------------- --------------------- "tmkeny" wrote in message ... 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 -------------------------------------------------------------------------- - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying worksheets with hyperlinks to named cells | New Users to Excel | |||
copying a named range | Excel Discussion (Misc queries) | |||
Copying Named Ranges | Excel Discussion (Misc queries) | |||
Copying named lists used in drop downs from one workbook to anothe | Excel Worksheet Functions | |||
copying named range | Excel Discussion (Misc queries) |