Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting the formula from a cell
I need the reference in the cell (i.e. need to extract the formula) instead
of the value itself so I can use it in another formula. How can this be done? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting the formula from a cell
Use this small User Defined Function:
Public Function whatsInIt(r As Range) As String whatsInIt = r.Formula End Function 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 - gsnu200781 "Khai N" wrote: I need the reference in the cell (i.e. need to extract the formula) instead of the value itself so I can use it in another formula. How can this be done? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting the formula from a cell
On Apr 28, 9:31*am, Khai N <Khai
wrote: I need the reference in the cell (i.e. need to extract the formula) instead of the value itself so I can use it in another formula. How can this be done? copy the original formula from the fx bar, not from the cell. Paste the formula into the new cell. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting the formula from a cell
"Gary''s Student" wrote:
Use this small User Defined Function: Public Function whatsInIt(r As Range) As String whatsInIt = r.Formula End Function Thanx! This was very helpfull! Works great! But to use this as an argument in another function I need it without the equal sign. I can't use the textfunction RIGHT() since it will convert to a textstring. Any idea? Regards, Khai N. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting the formula from a cell
Show me exactly how you want to use it in a worksheet cell.
-- Gary''s Student - gsnu200781 "Khai N" wrote: "Gary''s Student" wrote: Use this small User Defined Function: Public Function whatsInIt(r As Range) As String whatsInIt = r.Formula End Function Thanx! This was very helpfull! Works great! But to use this as an argument in another function I need it without the equal sign. I can't use the textfunction RIGHT() since it will convert to a textstring. Any idea? Regards, Khai N. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting the formula from a cell
"Gary''s Student" wrote:
Show me exactly how you want to use it in a worksheet cell. I'm making an 'import sheet' from Excel to a consolidation program (Cognos Controller). It takes values from a Excel sheet by using a simple reference ='Balanse 3018'!G5 However, to find the correct account to post the value in G5, Excel have to look up the name of the account in an unsorted list. This is done by using this command =INDEX(Kontoer!$A$51:$B$111;MATCH('Balanse 3018'!$B5;Kontoer!$A$51:$A$111;0);2) So far so good. But since I'm doing this for several workbooks (many companies) and many lines in each company (P/L statement, Balance, Equity, Assets) the prosess of updating the value reference (G5) and the argument in MATCH(...) - $B5 is tedious (i.e. changing the row) and vulnerable to error. I want that $B5 (that is, the row reference "5") updates automaticly when I change the value reference so that I only need to change it one place. Sounds easy at first, but not that easy after all! So how can I extract the row from your UDF (whatsinit) and paste it into the MATCH formula? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I make a blank cell in a formula cell with a range of cell | Excel Discussion (Misc queries) | |||
adding a formula in a cell but when cell = 0 cell is blank | Excel Worksheet Functions | |||
Cell Formula reference to cell Based On third Cell Content | Excel Discussion (Misc queries) | |||
Cell Formula reference to cell Based On third Cell Content | Excel Discussion (Misc queries) | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |