Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
In cell A1, I have 100. In cell A2, I have +A1+150 or 250. I need a
formula for cell A3 that points to cell A2 and gives me just the +150 part of the formula so that A3=150. Thanks |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
=A2-A1
"snax500" wrote: In cell A1, I have 100. In cell A2, I have +A1+150 or 250. I need a formula for cell A3 that points to cell A2 and gives me just the +150 part of the formula so that A3=150. Thanks |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I don't want the difference. I want a formula to pull out the 150 so I
can use it in another part of my file. On Nov 6, 2:58*pm, Joel wrote: =A2-A1 "snax500" wrote: In cell A1, I have 100. In cell A2, I have +A1+150 or 250. I need a formula for cell A3 that points to cell A2 and gives me just the +150 part of the formula so that A3=150. Thanks- Hide quoted text - - Show quoted text - |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
On Nov 6, 3:16*pm, snax500 wrote:
I don't want the difference. I want a formula to pull out the 150 so I can use it in another part of my file. On Nov 6, 2:58*pm, Joel wrote: Another example is if I had a cell with this formula - A1... =+[Forecast.xls]June!$A$47+300 and in another cell -A2 - I have a formula that looks up cell A1 and gives me just the +300 of the formula. thx =A2-A1 "snax500" wrote: In cell A1, I have 100. In cell A2, I have +A1+150 or 250. I need a formula for cell A3 that points to cell A2 and gives me just the +150 part of the formula so that A3=150. Thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
If you want a formula to give you 150, the formula is =150
-- David Biddulph "snax500" wrote in message ... I don't want the difference. I want a formula to pull out the 150 so I can use it in another part of my file. On Nov 6, 2:58 pm, Joel wrote: =A2-A1 "snax500" wrote: In cell A1, I have 100. In cell A2, I have +A1+150 or 250. I need a formula for cell A3 that points to cell A2 and gives me just the +150 part of the formula so that A3=150. Thanks- Hide quoted text - - Show quoted text - |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Ok, here is another example, I have the following example, in cell
A1... =+'[Forecast.xls]June'!$CU$23+3200 in cell A2, I want a formula to read A1 and give me just the +3200. Cell A2 will equal +3200. Thanks On Nov 6, 3:34*pm, "David Biddulph" <groups [at] biddulph.org.uk wrote: If you want a formula to give you 150, the formula is =150 -- David Biddulph "snax500" wrote in message ... I don't want the difference. I want a formula to pull out the 150 so I can use it in another part of my file. On Nov 6, 2:58 pm, Joel wrote: =A2-A1 "snax500" wrote: In cell A1, I have 100. In cell A2, I have +A1+150 or 250. I need a formula for cell A3 that points to cell A2 and gives me just the +150 part of the formula so that A3=150. Thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#7
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Ok, here is another example, I have the following example, in cell
A1... =+'[Forecast.xls]June'!$CU$23+3200 in cell A2, I want a formula to read A1 and give me just the +3200. Cell A2 will equal +3200. Thanks On Nov 6, 3:34*pm, "David Biddulph" <groups [at] biddulph.org.uk wrote: If you want a formula to give you 150, the formula is =150 -- David Biddulph "snax500" wrote in message ... I don't want the difference. I want a formula to pull out the 150 so I can use it in another part of my file. On Nov 6, 2:58 pm, Joel wrote: =A2-A1 "snax500" wrote: In cell A1, I have 100. In cell A2, I have +A1+150 or 250. I need a formula for cell A3 that points to cell A2 and gives me just the +150 part of the formula so that A3=150. Thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#8
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
To start with, you don't need =+ at the start of your formula. Just = will
do. You've already been told the answer to your question by Joel. If A2 contains =[Forecast.xls]June!$A$47+300 and you want to return 300 in another cell, the formula will be =A2-[Forecast.xls]June!$A$47 -- David Biddulph "snax500" wrote in message ... On Nov 6, 3:16 pm, snax500 wrote: I don't want the difference. I want a formula to pull out the 150 so I can use it in another part of my file. On Nov 6, 2:58 pm, Joel wrote: Another example is if I had a cell with this formula - A1... =+[Forecast.xls]June!$A$47+300 and in another cell -A2 - I have a formula that looks up cell A1 and gives me just the +300 of the formula. =A2-A1 |
#9
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I was thinking along the lines of an @right but with formulas not
text. On Nov 6, 3:47*pm, "David Biddulph" <groups [at] biddulph.org.uk wrote: To start with, you don't need =+ at the start of your formula. *Just = will do. You've already been told the answer to your question by Joel. *If A2 contains =[Forecast.xls]June!$A$47+300 and you want to return 300 in another cell, the formula will be =A2-[Forecast.xls]June!$A$47 -- David Biddulph "snax500" wrote in message ... On Nov 6, 3:16 pm, snax500 wrote: I don't want the difference. I want a formula to pull out the 150 so I can use it in another part of my file. On Nov 6, 2:58 pm, Joel wrote: Another example is if I had a cell with this formula - A1... =+[Forecast.xls]June!$A$47+300 and in another cell -A2 - I have a formula that looks up cell A1 and gives me just the +300 of the formula. =A2-A1- Hide quoted text - - Show quoted text - |
#10
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I don't understand why the solutions given do not work for you? This number
you are adding is static. Why do you need a formula to tell you a static number for other cells? I guess the only reason I can think of is that at times, this number will change, and you want all other references to still remain the same. I recommend the following in that case: A1: ='[Forecast.xls]June'!$CU$23+$A$2 A2: 3200 .... or if you prefer =3200 -- ** John C ** "snax500" wrote: In cell A1, I have 100. In cell A2, I have +A1+150 or 250. I need a formula for cell A3 that points to cell A2 and gives me just the +150 part of the formula so that A3=150. Thanks |
#11
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I was thinking along the line of an @right formula for formulas
instead of text. On Nov 6, 3:47*pm, "David Biddulph" <groups [at] biddulph.org.uk wrote: To start with, you don't need =+ at the start of your formula. *Just = will do. You've already been told the answer to your question by Joel. *If A2 contains =[Forecast.xls]June!$A$47+300 and you want to return 300 in another cell, the formula will be =A2-[Forecast.xls]June!$A$47 -- David Biddulph "snax500" wrote in message ... On Nov 6, 3:16 pm, snax500 wrote: I don't want the difference. I want a formula to pull out the 150 so I can use it in another part of my file. On Nov 6, 2:58 pm, Joel wrote: Another example is if I had a cell with this formula - A1... =+[Forecast.xls]June!$A$47+300 and in another cell -A2 - I have a formula that looks up cell A1 and gives me just the +300 of the formula. =A2-A1- Hide quoted text - - Show quoted text - |
#12
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Thanks John but it is a corporate form that can not be changed. I am
stuck with it the way it is ='[Forecast.xls]June'!$CU$23+3200 If anyone else has any other ideas I would appreciate it, like find the "+" and give me everything to the right of it. Thanks On Nov 6, 3:56*pm, John C <johnc@stateofdenial wrote: I don't understand why the solutions given do not work for you? This number you are adding is static. Why do you need a formula to tell you a static number for other cells? I guess the only reason I can think of is that at times, this number will change, and you want all other references to still remain the same. I recommend the following in that case: A1: ='[Forecast.xls]June'!$CU$23+$A$2 A2: 3200 *.... or if you prefer =3200 -- ** John C ** "snax500" wrote: In cell A1, I have 100. In cell A2, I have +A1+150 or 250. I need a formula for cell A3 that points to cell A2 and gives me just the +150 part of the formula so that A3=150. Thanks- Hide quoted text - - Show quoted text - |
#13
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Then the formula you have been given by David and by Joel works just fine.
Say, for example, that your formula is in A1, then =A1-'[Forecast.xls]June'!$CU$23 should work just fine. -- ** John C ** "snax500" wrote: Thanks John but it is a corporate form that can not be changed. I am stuck with it the way it is ='[Forecast.xls]June'!$CU$23+3200 If anyone else has any other ideas I would appreciate it, like find the "+" and give me everything to the right of it. Thanks On Nov 6, 3:56 pm, John C <johnc@stateofdenial wrote: I don't understand why the solutions given do not work for you? This number you are adding is static. Why do you need a formula to tell you a static number for other cells? I guess the only reason I can think of is that at times, this number will change, and you want all other references to still remain the same. I recommend the following in that case: A1: ='[Forecast.xls]June'!$CU$23+$A$2 A2: 3200 .... or if you prefer =3200 -- ** John C ** "snax500" wrote: In cell A1, I have 100. In cell A2, I have +A1+150 or 250. I need a formula for cell A3 that points to cell A2 and gives me just the +150 part of the formula so that A3=150. Thanks- Hide quoted text - - Show quoted text - |
#14
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
On Thu, 6 Nov 2008 13:03:46 -0800 (PST), snax500
wrote: Thanks John but it is a corporate form that can not be changed. I am stuck with it the way it is ='[Forecast.xls]June'!$CU$23+3200 If anyone else has any other ideas I would appreciate it, like find the "+" and give me everything to the right of it. Thanks Perhaps if you give some real examples where you tried the attempted solutions, and show us your original data; how you implemented the recommended solution; the actual result and the desired result, some one could figure out a proper solution. But what you've been repeating has not made clear why the recommended solutions won't work. --ron |
#15
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I have that formual in each month for this years actuals, this years
budget, last years actuals or 12*3 or 36 cells. It just would be easier if I could of had an @right formual and copied it accross. On Nov 6, 4:15*pm, Ron Rosenfeld wrote: On Thu, 6 Nov 2008 13:03:46 -0800 (PST), snax500 wrote: Thanks John but it is a corporate form that can not be changed. I am stuck with it the way it is ='[Forecast.xls]June'!$CU$23+3200 If anyone else has any other ideas I would appreciate it, like find the "+" and give me everything to the right of it. Thanks Perhaps if you give some real examples where you tried the attempted solutions, and show us your original data; how you implemented the recommended solution; the actual result and the desired result, some one could figure out a proper solution. But what you've been repeating has not made clear why the recommended solutions won't work. --ron |
#16
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
On Thu, 6 Nov 2008 13:27:39 -0800 (PST), snax500
wrote: I have that formual in each month for this years actuals, this years budget, last years actuals or 12*3 or 36 cells. It just would be easier if I could of had an @right formual and copied it accross. Perhaps if you give some real examples where you tried the attempted solutions, and show us your original data; how you implemented the recommended solution; the actual result and the desired result, some one could figure out a proper solution. But what you've been repeating has not made clear why the recommended solutions won't work. --ron |
#17
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Ok Try this :
=MID(C3,FIND("+",C3,FIND("!",C3)),99) here i have taken the cell where the data (=+'[Forecast.xls]June'!$CU$23+3200) is given. now what i have done is first find "!" mark and then fine "+" sign after that....this formula will extract "+3200" -- Kind Regards, Satti Charvak Only an Excel Enthusiast Noida, India "snax500" wrote: Ok, here is another example, I have the following example, in cell A1... =+'[Forecast.xls]June'!$CU$23+3200 in cell A2, I want a formula to read A1 and give me just the +3200. Cell A2 will equal +3200. Thanks On Nov 6, 3:34 pm, "David Biddulph" <groups [at] biddulph.org.uk wrote: If you want a formula to give you 150, the formula is =150 -- David Biddulph "snax500" wrote in message ... I don't want the difference. I want a formula to pull out the 150 so I can use it in another part of my file. On Nov 6, 2:58 pm, Joel wrote: =A2-A1 "snax500" wrote: In cell A1, I have 100. In cell A2, I have +A1+150 or 250. I need a formula for cell A3 that points to cell A2 and gives me just the +150 part of the formula so that A3=150. Thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#18
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I think you'll find, Satti, that your formula will work if C3 contains a
text string, but not if C3 contains a formula. -- David Biddulph "Satti Charvak" wrote in message ... Ok Try this : =MID(C3,FIND("+",C3,FIND("!",C3)),99) here i have taken the cell where the data (=+'[Forecast.xls]June'!$CU$23+3200) is given. now what i have done is first find "!" mark and then fine "+" sign after that....this formula will extract "+3200" -- Kind Regards, Satti Charvak Only an Excel Enthusiast Noida, India "snax500" wrote: Ok, here is another example, I have the following example, in cell A1... =+'[Forecast.xls]June'!$CU$23+3200 in cell A2, I want a formula to read A1 and give me just the +3200. Cell A2 will equal +3200. Thanks On Nov 6, 3:34 pm, "David Biddulph" <groups [at] biddulph.org.uk wrote: If you want a formula to give you 150, the formula is =150 -- David Biddulph "snax500" wrote in message ... I don't want the difference. I want a formula to pull out the 150 so I can use it in another part of my file. On Nov 6, 2:58 pm, Joel wrote: =A2-A1 "snax500" wrote: In cell A1, I have 100. In cell A2, I have +A1+150 or 250. I need a formula for cell A3 that points to cell A2 and gives me just the +150 part of the formula so that A3=150. Thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#19
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Well, if the OP is still interested, he could search the groups to find the
information for UDF GETFORMULA, and then using Satti's formula, it would be: =--SUBSTITUTE(MID(getformula(A10),FIND("!",getformula (A10),FIND("=",getformula(A10))),99),"+","") This would give the numeric equivalent after the + sign. Of course, if there are multiple plus signs, or if the formula doesn't refer to a separate WB, or if the OP doesn't have the UDF on every computer that will be using the file, then it will fail, but hey, it's what the OP wanted, right? I don't understand why the other extremely simple formulas won't work, but this is the answer to the OP's question. -- ** John C ** "David Biddulph" wrote: I think you'll find, Satti, that your formula will work if C3 contains a text string, but not if C3 contains a formula. -- David Biddulph "Satti Charvak" wrote in message ... Ok Try this : =MID(C3,FIND("+",C3,FIND("!",C3)),99) here i have taken the cell where the data (=+'[Forecast.xls]June'!$CU$23+3200) is given. now what i have done is first find "!" mark and then fine "+" sign after that....this formula will extract "+3200" -- Kind Regards, Satti Charvak Only an Excel Enthusiast Noida, India "snax500" wrote: Ok, here is another example, I have the following example, in cell A1... =+'[Forecast.xls]June'!$CU$23+3200 in cell A2, I want a formula to read A1 and give me just the +3200. Cell A2 will equal +3200. Thanks On Nov 6, 3:34 pm, "David Biddulph" <groups [at] biddulph.org.uk wrote: If you want a formula to give you 150, the formula is =150 -- David Biddulph "snax500" wrote in message ... I don't want the difference. I want a formula to pull out the 150 so I can use it in another part of my file. On Nov 6, 2:58 pm, Joel wrote: =A2-A1 "snax500" wrote: In cell A1, I have 100. In cell A2, I have +A1+150 or 250. I need a formula for cell A3 that points to cell A2 and gives me just the +150 part of the formula so that A3=150. Thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#20
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Whoops, should have been:
I had different sample data, but the premise is the same. I think the OP has abandoned this thread, so, not gonna sweat it any more. -- ** John C ** "John C" wrote: Well, if the OP is still interested, he could search the groups to find the information for UDF GETFORMULA, and then using Satti's formula, it would be: =--SUBSTITUTE(MID(getformula(A10),FIND("!",getformula (A10),FIND("=",getformula(A10))),99),"+","") This would give the numeric equivalent after the + sign. Of course, if there are multiple plus signs, or if the formula doesn't refer to a separate WB, or if the OP doesn't have the UDF on every computer that will be using the file, then it will fail, but hey, it's what the OP wanted, right? I don't understand why the other extremely simple formulas won't work, but this is the answer to the OP's question. -- ** John C ** "David Biddulph" wrote: I think you'll find, Satti, that your formula will work if C3 contains a text string, but not if C3 contains a formula. -- David Biddulph "Satti Charvak" wrote in message ... Ok Try this : =MID(C3,FIND("+",C3,FIND("!",C3)),99) here i have taken the cell where the data (=+'[Forecast.xls]June'!$CU$23+3200) is given. now what i have done is first find "!" mark and then fine "+" sign after that....this formula will extract "+3200" -- Kind Regards, Satti Charvak Only an Excel Enthusiast Noida, India "snax500" wrote: Ok, here is another example, I have the following example, in cell A1... =+'[Forecast.xls]June'!$CU$23+3200 in cell A2, I want a formula to read A1 and give me just the +3200. Cell A2 will equal +3200. Thanks On Nov 6, 3:34 pm, "David Biddulph" <groups [at] biddulph.org.uk wrote: If you want a formula to give you 150, the formula is =150 -- David Biddulph "snax500" wrote in message ... I don't want the difference. I want a formula to pull out the 150 so I can use it in another part of my file. On Nov 6, 2:58 pm, Joel wrote: =A2-A1 "snax500" wrote: In cell A1, I have 100. In cell A2, I have +A1+150 or 250. I need a formula for cell A3 that points to cell A2 and gives me just the +150 part of the formula so that A3=150. Thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Search/Match/Find ANY part of string to ANY part of Cell Value | Excel Worksheet Functions | |||
copy selected part number of text from one cell into another cell | Excel Discussion (Misc queries) | |||
Display contents of cell in another cell as part of text string? | New Users to Excel | |||
Using macro to copy a part of a cell content to next cell | Excel Discussion (Misc queries) | |||
Can you make a cell with a diagonal, so that you can edit the upper and lower part of the cell? | New Users to Excel |