execute once formula

[tekman]

I have a spreadsheet that monitors a component failure based on several criteria. One column has a formula that enters the date that returns a failure date if the component fails, and leaves the cell blank if the component is functioning normally.

(Column P contains the following)

Please Login or Register  to view this content.

Where N is the column that shows the component has failed and Summary!$B$2 contains the date that the sheet was updated. This works as intended, but I would like the cell to "lose" the formula after it returns a value so that the date will "stick". This way I can track the original failed date even after the component has been repaired.

I can manually do this by filtering for non-blank cells and copy/paste special (values), but there are about 1500 rows in this spreadsheet and it is updated daily so this is rather cumbersome. The only way that I can think to do this is a circular function (which obviously does not work) ie:

(Wishful thinking for Column P)

Please Login or Register  to view this content.

I have a brain block on how to automate this task, either with a UDF or a VBA Macro (either would be fine)

Thanks,

Lee

[K Dales]

Here is one way to do it using the Worksheet_Calculate Event Procedure (you
can keep the formula in column P the same as you currently have; this will
just check column P after it calculates and will find the non-blank results,
will erase the formula and replace it with the value):

Private Sub Worksheet_Calculate()
Dim PRange As Range, PCell As Range
On Error GoTo NoCells
Set PRange = ActiveSheet.Range("P:P").SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
For Each PCell In PRange.Cells
If PCell.Value <> "" Then PCell.Formula = PCell.Value
Next PCell
NoCells:
End Sub

--
- K Dales


"tekman" wrote:

>
> I have a spreadsheet that monitors a component failure based on several
> criteria. One column has a formula that enters the date that returns a
> failure date if the component fails, and leaves the cell blank if the
> component is functioning normally.
>
> (Column P contains the following)
>
> Code:
> --------------------
> =IF(N1="Failed",Summary!$B$2,"")
> --------------------
>
>
> Where N is the column that shows the component has failed and
> Summary!$B$2 contains the date that the sheet was updated. This works
> as intended, but I would like the cell to "lose" the formula after it
> returns a value so that the date will "stick". This way I can track the
> original failed date even after the component has been repaired.
>
> I can manually do this by filtering for non-blank cells and copy/paste
> special (values), but there are about 1500 rows in this spreadsheet and
> it is updated daily so this is rather cumbersome. The only way that I
> can think to do this is a circular function (which obviously does not
> work) ie:
>
> (Wishful thinking for Column P)
>
> Code:
> --------------------
> =IF(and(N1="Failed",P1=""),Summary!$B$2,P1)
> --------------------
>
>
> I have a brain block on how to automate this task, either with a UDF or
> a VBA Macro (either would be fine)
>
> Thanks,
>
> Lee
>
>
> --
> tekman
> ------------------------------------------------------------------------
> tekman's Profile: http://www.excelforum.com/member.php...o&userid=28843
> View this thread: http://www.excelforum.com/showthread...hreadid=491352
>
>

[tekman]

I will play with this one my plane ride this afternoon!!

Lee