How do I convert a cell(s) from the formula to it's contents?

[ZZBC]

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!

 

[Ken Wright]

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]

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.

 

[Philippe L. Balmanno]

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.

 

[Otto Moehrbach]

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]

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

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
?

 

[ZZBC]

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

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

 

[Spike9458]

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

: 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!

 

[Otto Moehrbach]

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

 

[Otto Moehrbach]

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