Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to read formula in a cell
In cell 'A1' I have the formula:
=A2+A3 In B1, is there a way I can write a formula that will grab the first cell of that formula in 'A1' (in this example the answer would be A2). Thanks, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to read formula in a cell
First install the following User Defined Function:
Public Function txet(r As Range) As String txet = r.Formula End Function and then in B1: =LEFT(RIGHT(txet(A1),LEN(txet(A1))-1),FIND("+",txet(A1))-2) UDFs are very easy to install and use: 1. ALT-F11 brings up the VBE window 2. ALT-I ALT-M opens a fresh module 3. paste the stuff in and close the VBE window If you save the workbook, the UDF will be saved with it. To use the UDF from the normal Excel window, just enter it like a normal Excel Function To remove the UDF: 1. bring up the VBE window as above 2. clear the code out 3. close the VBE window To learn more about UDFs, see: http://www.cpearson.com/excel/Writin...ionsInVBA.aspx -- Gary''s Student - gsnu200800 " wrote: In cell 'A1' I have the formula: =A2+A3 In B1, is there a way I can write a formula that will grab the first cell of that formula in 'A1' (in this example the answer would be A2). Thanks, |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to read formula in a cell
You can convert a working XL formula into Text that can then be parsed to
return whatever part of the formula you need. This is easily accomplished by incorporating an old XL 4.0 macro into a *relative* named formula. We'll configure this named formula to reference the column just to the left of *any cell* containing this formula, though it can be configured to work on *any* cell that's relative to the location of the formula containing cell. Click in B1. Doesn't matter if it's empty or populated. From the Menu Bar: <Insert. <Name <Define In the "Names In Workbook" box, enter a short name for this formula, let's use disp to denote display. Then, change whatever's in the "Refers To" box to: =Get.Cell(6,A1) Then <OK With a *relative* reference to A1 (no $ signs), and since you clicked in B1 at the start, this formula will work on any cell to the immediate left of *any* cell that contains this formula. So, say in C1 you entered: Tyson In D1 enter =disp and you'll get "Tyson" returned. Now, in C2 enter the formula: =A1+A2 and in D2 enter: =disp And you'll get the formula returned, =A1+A2 *not* the results of the formula. Now, to use a Text formula to parse the formula itself: Enter this formula in any cell to the immediate right of the cell containing the formula you wish to parse: =MID(disp,2,FIND("+",disp)-2) This will return the characters between the equal sign and the first + sign. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ... In cell 'A1' I have the formula: =A2+A3 In B1, is there a way I can write a formula that will grab the first cell of that formula in 'A1' (in this example the answer would be A2). Thanks, |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to read formula in a cell
Forgot the caveat!
XL 4.0 macros can be used in all versions of XL, *BUT* In versions before XL02, you should *not* copy them between *workbooks*! They *can* be copied between sheets within a workbook. The versions prior to XL02 will crash if an attempt is made to copy between *workbooks*. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "RagDyer" wrote in message ... You can convert a working XL formula into Text that can then be parsed to return whatever part of the formula you need. This is easily accomplished by incorporating an old XL 4.0 macro into a *relative* named formula. We'll configure this named formula to reference the column just to the left of *any cell* containing this formula, though it can be configured to work on *any* cell that's relative to the location of the formula containing cell. Click in B1. Doesn't matter if it's empty or populated. From the Menu Bar: <Insert. <Name <Define In the "Names In Workbook" box, enter a short name for this formula, let's use disp to denote display. Then, change whatever's in the "Refers To" box to: =Get.Cell(6,A1) Then <OK With a *relative* reference to A1 (no $ signs), and since you clicked in B1 at the start, this formula will work on any cell to the immediate left of *any* cell that contains this formula. So, say in C1 you entered: Tyson In D1 enter =disp and you'll get "Tyson" returned. Now, in C2 enter the formula: =A1+A2 and in D2 enter: =disp And you'll get the formula returned, =A1+A2 *not* the results of the formula. Now, to use a Text formula to parse the formula itself: Enter this formula in any cell to the immediate right of the cell containing the formula you wish to parse: =MID(disp,2,FIND("+",disp)-2) This will return the characters between the equal sign and the first + sign. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ... In cell 'A1' I have the formula: =A2+A3 In B1, is there a way I can write a formula that will grab the first cell of that formula in 'A1' (in this example the answer would be A2). Thanks, |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to read formula in a cell
Great! Thanks for the help!!
On Aug 19, 12:34*pm, "RagDyer" wrote: Forgot the caveat! XL 4.0 macros can be used in all versions of XL, *BUT* In versions before XL02, you should *not* copy them between *workbooks*! They *can* be copied between sheets within a workbook. The versions prior to XL02 will crash if an attempt is made to copy between *workbooks*. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "RagDyer" wrote in message ... You can convert a working XL formula into Text that can then be parsed to return whatever part of the formula you need. This is easily accomplished by incorporating an old XL 4.0 macro into a *relative* named formula. We'll configure this named formula to reference the column just to the left of *any cell* containing this formula, though it can be configured to work on *any* cell that's relative to the location of the formula containing cell. Click in B1. Doesn't matter if it's empty or populated. From the Menu Bar: <Insert. <Name <Define In the "Names In Workbook" box, enter a short name for this formula, let's use disp to denote display. Then, change whatever's in the "Refers To" box to: =Get.Cell(6,A1) Then <OK With a *relative* reference to A1 (no $ signs), and since you clicked in B1 at the start, this formula will work on any cell to the immediate left of *any* cell that contains this formula. So, say in C1 you entered: Tyson In D1 enter =disp and you'll get "Tyson" returned. Now, in C2 enter the formula: =A1+A2 and in D2 enter: =disp And you'll get the formula returned, =A1+A2 *not* the results of the formula. Now, to use a Text formula to parse the formula itself: Enter this formula in any cell to the immediate right of the cell containing the formula you wish to parse: =MID(disp,2,FIND("+",disp)-2) This will return the characters between the equal sign and the first + sign. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message .... In cell 'A1' I have the formula: =A2+A3 In B1, is there a way I can write a formula that will grab the first cell of that formula in 'A1' (in this example the answer would be A2). Thanks,- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to read formula in a cell
You're welcome, and appreciate the feed-back.
-- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- wrote in message ... Great! Thanks for the help!! On Aug 19, 12:34 pm, "RagDyer" wrote: Forgot the caveat! XL 4.0 macros can be used in all versions of XL, *BUT* In versions before XL02, you should *not* copy them between *workbooks*! They *can* be copied between sheets within a workbook. The versions prior to XL02 will crash if an attempt is made to copy between *workbooks*. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "RagDyer" wrote in message ... You can convert a working XL formula into Text that can then be parsed to return whatever part of the formula you need. This is easily accomplished by incorporating an old XL 4.0 macro into a *relative* named formula. We'll configure this named formula to reference the column just to the left of *any cell* containing this formula, though it can be configured to work on *any* cell that's relative to the location of the formula containing cell. Click in B1. Doesn't matter if it's empty or populated. From the Menu Bar: <Insert. <Name <Define In the "Names In Workbook" box, enter a short name for this formula, let's use disp to denote display. Then, change whatever's in the "Refers To" box to: =Get.Cell(6,A1) Then <OK With a *relative* reference to A1 (no $ signs), and since you clicked in B1 at the start, this formula will work on any cell to the immediate left of *any* cell that contains this formula. So, say in C1 you entered: Tyson In D1 enter =disp and you'll get "Tyson" returned. Now, in C2 enter the formula: =A1+A2 and in D2 enter: =disp And you'll get the formula returned, =A1+A2 *not* the results of the formula. Now, to use a Text formula to parse the formula itself: Enter this formula in any cell to the immediate right of the cell containing the formula you wish to parse: =MID(disp,2,FIND("+",disp)-2) This will return the characters between the equal sign and the first + sign. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ... In cell 'A1' I have the formula: =A2+A3 In B1, is there a way I can write a formula that will grab the first cell of that formula in 'A1' (in this example the answer would be A2). Thanks,- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to read just the first 2 to 3 characters in a cell. | Excel Discussion (Misc queries) | |||
CAN YOU SET GRAPH TO READ, FORMULA IN A CELL AS A BLANK CELL | Excel Discussion (Misc queries) | |||
How do I make a formula read a result rather than a formula | Excel Discussion (Misc queries) | |||
How would this formula read | Excel Discussion (Misc queries) | |||
can formula to read sheetname from a cell? | Excel Worksheet Functions |