If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
return a formula instead of a value of a cell
Is it possible to let excel return a formula instead of the cells value. At
this stage I need to write all possible formula's for each cell (vlookup will return the corret one depending on a code inserted by user). I was wondering if it was possible to return a formula instead of a value. This would make my lookup table considirable smaler and avoids repeating all formulas 60 times. And I only have to edit one formula/cell to correct mistakes. Example: Cell B3 on Sheet2 contains the following O&row/P&row * Q&row I want excel to actually return this formala to the cell on sheet1 and not the result. thanks |
#2
|
|||
|
|||
return a formula instead of a value of a cell
You could use a UDF (User Defined Function) that returns a string--but it's not
a formula anymore. willemeulen wrote: Is it possible to let excel return a formula instead of the cells value. At this stage I need to write all possible formula's for each cell (vlookup will return the corret one depending on a code inserted by user). I was wondering if it was possible to return a formula instead of a value. This would make my lookup table considirable smaler and avoids repeating all formulas 60 times. And I only have to edit one formula/cell to correct mistakes. Example: Cell B3 on Sheet2 contains the following O&row/P&row * Q&row I want excel to actually return this formala to the cell on sheet1 and not the result. thanks -- Dave Peterson |
#3
|
|||
|
|||
return a formula instead of a value of a cell
Here is a User Defined FUnction to return the formula:
Function formulaa(r As Range) As String formulaa = 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: =formulaa(Sheet1!A1) 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 - gsnu200853 "willemeulen" wrote: Is it possible to let excel return a formula instead of the cells value. At this stage I need to write all possible formula's for each cell (vlookup will return the corret one depending on a code inserted by user). I was wondering if it was possible to return a formula instead of a value. This would make my lookup table considirable smaler and avoids repeating all formulas 60 times. And I only have to edit one formula/cell to correct mistakes. Example: Cell B3 on Sheet2 contains the following O&row/P&row * Q&row I want excel to actually return this formala to the cell on sheet1 and not the result. thanks |
Thread Tools | |
Display Modes | |
|
|