Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference Cell Content
How would I reference the content of a cell instead of the result?
Example: A1: =IF(1,2,0) (Result is 2 formatted as number) A2: ???? (Result is "=IF(1,2,0)" formatted as text) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference Cell Content
Sloth wrote...
How would I reference the content of a cell instead of the result? Example: A1: =IF(1,2,0) (Result is 2 formatted as number) A2: ???? (Result is "=IF(1,2,0)" formatted as text) Excel provides no built-in support for this, so you need to use programming or add-ins. The simplistic approach would be to use XLM function calls in defined names. Given your example, move to cell A2, define a name like f1up referring to =GET.FORMULA(A1) and enter the formula =f1up in cell A2. The BIG drawback to this is that in older versions of Excel (2000 and prior), copying a cell with a formula referring to defined names that call XLM functions and trying to paste into other worksheets will CRASH Excel. The lesser drawback is that this approach is inflexible. The programming option involves writing a user-defined function in VBA. Put the following code in a general VBA module. Function ft(r As Range) As String ft = r.Areas(1).Cells(1, 1).Formula End Function In your example, enter the formula =ft(A1) in cell A2. The add-in approach involves downloading and installing Laurent Longre's MOREFUNC.XLL add-in, available from http://xcell05.free.fr/english/ . It provides a function names FORMULATEXT that does what you want. Enter =FORMULATEXT(A1) in cell A2. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
Nesting a sheet name reference within a cell reference??? | Excel Discussion (Misc queries) | |||
Cell reference problem | Excel Worksheet Functions | |||
how do I format a cell reference to move as source changes | Excel Worksheet Functions | |||
Flexible Cell Reference | Excel Discussion (Misc queries) |