Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I convert a cell(s) from the formula to it's contents?
Using Excell 2000 ...
I have data being retrieved from another sheet Cell A1 of the Input Sheet, for example might contain the words "This is a TEST" Cell A1 of the Output Sheet contains the formula: ='Input Sheet'!$A$1 .... thus displaying whatever the contents is of cell A1 of the Input Sheet. I want to save the formula results in time ... I want to convert the cell(s) of the Output Sheet to the results ... getting rid of the equation(s). I'm sure I have seen this before, I just cannot remember ??? ... getting old! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I convert a cell(s) from the formula to it's contents?
You can do it via code, but thats it. There is no function/formula that
will convert itself to text automatically. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "ZZBC" wrote in message ... Using Excell 2000 ... I have data being retrieved from another sheet Cell A1 of the Input Sheet, for example might contain the words "This is a TEST" Cell A1 of the Output Sheet contains the formula: ='Input Sheet'!$A$1 ... thus displaying whatever the contents is of cell A1 of the Input Sheet. I want to save the formula results in time ... I want to convert the cell(s) of the Output Sheet to the results ... getting rid of the equation(s). I'm sure I have seen this before, I just cannot remember ??? ... getting old! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I convert a cell(s) from the formula to it's contents?
Ken Wright wrote:
You can do it via code, but thats it. There is no function/formula that will convert itself to text automatically. Can you give me an overview of how to do it in code so I would not take too many wrong paths ... I may try it? I've did some coding in Visual Basic ... 2-3 years ago ... I would have to get my mind back into it. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I convert a cell(s) from the formula to it's contents?
"ZZBC" wrote in message
... Using Excell 2000 ... I have data being retrieved from another sheet Cell A1 of the Input Sheet, for example might contain the words "This is a TEST" Cell A1 of the Output Sheet contains the formula: ='Input Sheet'!$A$1 ... thus displaying whatever the contents is of cell A1 of the Input Sheet. I want to save the formula results in time ... I want to convert the cell(s) of the Output Sheet to the results ... getting rid of the equation(s). I'm sure I have seen this before, I just cannot remember ??? ... getting old! If you want to do it mannually each time, "copy" and "paste special" making sure you check "values" option when the dialog box pops up. This will save the values without the formulas. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I convert a cell(s) from the formula to it's contents?
Doing this the way that you describe (you have a formula, you want to change
the formula to its value whenever its value changes) is a bit awkward. I say "awkward" because a Worksheet_Change event macro will not fire when the value of a formula changes, only when the content of a cell changes. This leaves the Worksheet_Calculate event macro as the only macro that will fire. The problem with this is that the Worksheet_Calculate macro does not have a target cell. The target cell would be the cell that triggered the calculation. Without a target cell, the code would have to check the destination cell value against the source cell value to see if they are the same. The code would also have to check if the destination cell contained a formula. If both conditions are met, the code would then change the formula to its value. If your data has multiple source and destination cells, the code would have to loop through all of them to find the one that fits both of the above criteria. A much easier approach is to use a Worksheet_Change event macro in the Input sheet. That macro would fire whenever the source cell changed. The code you write in that macro would then simply copy the contents of the target cell (source cell) to the destination cell in the Output sheet. A formula would not be needed in the destination cell at any time. An example of such a macro is: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 1 Then Exit Sub With Sheets("Output") .Range(Target.Address).Value = Target.Value End With End Sub Note that, as written, this macro considers the destination and source cells to have the same cell addresses. Please post back if you need more. HTH Otto "ZZBC" wrote in message ... Ken Wright wrote: You can do it via code, but thats it. There is no function/formula that will convert itself to text automatically. Can you give me an overview of how to do it in code so I would not take too many wrong paths ... I may try it? I've did some coding in Visual Basic ... 2-3 years ago ... I would have to get my mind back into it. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I convert a cell(s) from the formula to it's contents?
Otto Moehrbach wrote:
Doing this the way that you describe (you have a formula, you want to change the formula to its value whenever its value changes) is a bit awkward. I say "awkward" because a Worksheet_Change event macro will not fire when the value of a formula changes, only when the content of a cell changes. This leaves the Worksheet_Calculate event macro as the only macro that will fire. The problem with this is that the Worksheet_Calculate macro does not have a target cell. The target cell would be the cell that triggered the calculation. Without a target cell, the code would have to check the destination cell value against the source cell value to see if they are the same. The code would also have to check if the destination cell contained a formula. If both conditions are met, the code would then change the formula to its value. If your data has multiple source and destination cells, the code would have to loop through all of them to find the one that fits both of the above criteria. A much easier approach is to use a Worksheet_Change event macro in the Input sheet. That macro would fire whenever the source cell changed. The code you write in that macro would then simply copy the contents of the target cell (source cell) to the destination cell in the Output sheet. A formula would not be needed in the destination cell at any time. An example of such a macro is: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 1 Then Exit Sub With Sheets("Output") .Range(Target.Address).Value = Target.Value End With End Sub Note that, as written, this macro considers the destination and source cells to have the same cell addresses. Please post back if you need more. HTH Otto "ZZBC" wrote in message ... Ken Wright wrote: You can do it via code, but thats it. There is no function/formula that will convert itself to text automatically. Can you give me an overview of how to do it in code so I would not take too many wrong paths ... I may try it? I've did some coding in Visual Basic ... 2-3 years ago ... I would have to get my mind back into it. THANK YOU for saving me a lot of learning by 'trial and error' and especially for taking the time to write it down! I looked briefly at your example ... why the If Target.Column < 1 ? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I convert a cell(s) from the formula to it's contents?
Otto Moehrbach wrote:
Doing this the way that you describe (you have a formula, you want to change the formula to its value whenever its value changes) is a bit awkward. I say "awkward" because a Worksheet_Change event macro will not fire when the value of a formula changes, only when the content of a cell changes. This leaves the Worksheet_Calculate event macro as the only macro that will fire. The problem with this is that the Worksheet_Calculate macro does not have a target cell. The target cell would be the cell that triggered the calculation. Without a target cell, the code would have to check the destination cell value against the source cell value to see if they are the same. The code would also have to check if the destination cell contained a formula. If both conditions are met, the code would then change the formula to its value. If your data has multiple source and destination cells, the code would have to loop through all of them to find the one that fits both of the above criteria. A much easier approach is to use a Worksheet_Change event macro in the Input sheet. That macro would fire whenever the source cell changed. The code you write in that macro would then simply copy the contents of the target cell (source cell) to the destination cell in the Output sheet. A formula would not be needed in the destination cell at any time. An example of such a macro is: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 1 Then Exit Sub With Sheets("Output") .Range(Target.Address).Value = Target.Value End With End Sub Note that, as written, this macro considers the destination and source cells to have the same cell addresses. Please post back if you need more. HTH Otto "ZZBC" wrote in message ... Ken Wright wrote: You can do it via code, but thats it. There is no function/formula that will convert itself to text automatically. Can you give me an overview of how to do it in code so I would not take too many wrong paths ... I may try it? I've did some coding in Visual Basic ... 2-3 years ago ... I would have to get my mind back into it. Well, I created the macro ... doesn't seem to be doing anything ? I set a toggle point ... did not seem to get there. Any ideas? Bob |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I convert a cell(s) from the formula to it's contents?
I don't mean to oversimplify, but what happens if you select and copy the
data from the output sheet, and paste-special (click on "values") the data to the area you want the data to be. --Jim "ZZBC" wrote in message ... : Using Excell 2000 ... : I have data being retrieved from another sheet : Cell A1 of the Input Sheet, for example might contain the words "This is : a TEST" : Cell A1 of the Output Sheet contains the formula: ='Input Sheet'!$A$1 : ... thus displaying whatever the contents is of cell A1 of the Input Sheet. : I want to save the formula results in time ... : I want to convert the cell(s) of the Output Sheet to the results ... : getting rid of the equation(s). : I'm sure I have seen this before, I just cannot remember ??? ... getting : old! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I convert a cell(s) from the formula to it's contents?
The Worksheet_Change macro fires whenever a change occurs to the contents of
ANY cell in the sheet. ANY cell!. Usually, the user (you) wants something to happen only if the change occurs within some explicit range of cells. I arbitrarily chose Column A. The statement: If Target.Column < 1 says, simply, that if the target cell is not (<) in Column A (Column #1), to do nothing. You would need to adjust this to more properly fit with your data layout. HTH Otto "ZZBC" wrote in message ... Otto Moehrbach wrote: Doing this the way that you describe (you have a formula, you want to change the formula to its value whenever its value changes) is a bit awkward. I say "awkward" because a Worksheet_Change event macro will not fire when the value of a formula changes, only when the content of a cell changes. This leaves the Worksheet_Calculate event macro as the only macro that will fire. The problem with this is that the Worksheet_Calculate macro does not have a target cell. The target cell would be the cell that triggered the calculation. Without a target cell, the code would have to check the destination cell value against the source cell value to see if they are the same. The code would also have to check if the destination cell contained a formula. If both conditions are met, the code would then change the formula to its value. If your data has multiple source and destination cells, the code would have to loop through all of them to find the one that fits both of the above criteria. A much easier approach is to use a Worksheet_Change event macro in the Input sheet. That macro would fire whenever the source cell changed. The code you write in that macro would then simply copy the contents of the target cell (source cell) to the destination cell in the Output sheet. A formula would not be needed in the destination cell at any time. An example of such a macro is: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 1 Then Exit Sub With Sheets("Output") .Range(Target.Address).Value = Target.Value End With End Sub Note that, as written, this macro considers the destination and source cells to have the same cell addresses. Please post back if you need more. HTH Otto "ZZBC" wrote in message ... Ken Wright wrote: You can do it via code, but thats it. There is no function/formula that will convert itself to text automatically. Can you give me an overview of how to do it in code so I would not take too many wrong paths ... I may try it? I've did some coding in Visual Basic ... 2-3 years ago ... I would have to get my mind back into it. THANK YOU for saving me a lot of learning by 'trial and error' and especially for taking the time to write it down! I looked briefly at your example ... why the If Target.Column < 1 ? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I convert a cell(s) from the formula to it's contents?
Bob
You probably don't have the macro placed in the proper module. The macro I gave you is a sheet event macro. As such it must be placed in the sheet module for that sheet (the Input sheet). To access the sheet module for that sheet, first select that sheet. Then right-click on the sheet tab for that sheet. In the menu that pops up, select View Code. That brings up the sheet module. Paste the macro into that module. You can click the "X" in the top right corner of the module to return to the spreadsheet. HTH Otto "ZZBC" wrote in message ... Otto Moehrbach wrote: Doing this the way that you describe (you have a formula, you want to change the formula to its value whenever its value changes) is a bit awkward. I say "awkward" because a Worksheet_Change event macro will not fire when the value of a formula changes, only when the content of a cell changes. This leaves the Worksheet_Calculate event macro as the only macro that will fire. The problem with this is that the Worksheet_Calculate macro does not have a target cell. The target cell would be the cell that triggered the calculation. Without a target cell, the code would have to check the destination cell value against the source cell value to see if they are the same. The code would also have to check if the destination cell contained a formula. If both conditions are met, the code would then change the formula to its value. If your data has multiple source and destination cells, the code would have to loop through all of them to find the one that fits both of the above criteria. A much easier approach is to use a Worksheet_Change event macro in the Input sheet. That macro would fire whenever the source cell changed. The code you write in that macro would then simply copy the contents of the target cell (source cell) to the destination cell in the Output sheet. A formula would not be needed in the destination cell at any time. An example of such a macro is: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 1 Then Exit Sub With Sheets("Output") .Range(Target.Address).Value = Target.Value End With End Sub Note that, as written, this macro considers the destination and source cells to have the same cell addresses. Please post back if you need more. HTH Otto "ZZBC" wrote in message ... Ken Wright wrote: You can do it via code, but thats it. There is no function/formula that will convert itself to text automatically. Can you give me an overview of how to do it in code so I would not take too many wrong paths ... I may try it? I've did some coding in Visual Basic ... 2-3 years ago ... I would have to get my mind back into it. Well, I created the macro ... doesn't seem to be doing anything ? I set a toggle point ... did not seem to get there. Any ideas? Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible? | Excel Worksheet Functions | |||
quick way to copy-paste a formula linked to cells in another file | Excel Worksheet Functions | |||
If formula evaluating 2 cells contents | Excel Worksheet Functions | |||
Again-revealing the contents of formula | Excel Worksheet Functions | |||
Problem with VBA returning the contents of a long formula. | Excel Discussion (Misc queries) |