need formula to work once

[ercstric]

i have a logic formula that works great, but if its true i want the value to replace the formula automatically. this way when the target data changes it will not erase the previous answer.

thanks eric

[Tom Urtis]

You will need VBA to do this and you will need to modify this code to suit your situation.

Right click on your sheet tab, left click on View Code, and paste this in:

Private Sub Worksheet_Calculate()
If [A1] = 5 Then
[A1] = [A1]
End If
End Sub

This code demonstrates the example that, let's say, you have a formula in A1 such as
=B1+C1
The first time that a combination of numbers is entered into B1 and C1 that equals 5, then a 5 will be displayed in A1 and the formula in A1 will be overridden.

Hopefully this will help point you in the direction to accomplish this for your specific cell and True False equation.

Tom Urtis

[ercstric]

thanks, i'll try it

[ercstric]

thanks for the formula

Private Sub Worksheet_Calculate()
If [A1] = 5 Then
[A1] = [A1]
End If
End Sub

but i want [a1] = (a cell on another sheet in the workbook) sheet1 [b2]
how do i reference sheet1 ?
thanks again!
eric
--

[gplhl]

On 2002-02-26 16:00, ercstric wrote:
thanks for the formula

Private Sub Worksheet_Calculate()
If [A1] = 5 Then
[A1] = [A1]
End If
End Sub

but i want [a1] = (a cell on another sheet in the workbook) sheet1 [b2]
how do i reference sheet1 ?
thanks again!
eric
--

Use Range("[Book1.xls]Sheet1!A1").Value
Where Book1.xls is the name of the workbook and Sheet1! is the name of the sheet and A1 is the cell.

[ercstric]

i still have the problem ...

the cell is not retaining the solution to the formula. once the target cell entry changes, the cell i want frozen just goes back to being false

here is the formula:
=IF(A9='File Master'!B23,'File Master'!B2:F2,"")

a9 is a document number say 2002-06 (text format), then i want the title returned as the solution !b2:f2 if false then return empty cell ("")

[ This Message was edited by: ercstric on 2002-02-26 20:23 ]

[ This Message was edited by: ercstric on 2002-02-26 20:23 ]

[ercstric]

OKAY!!!!!!!! I got it!!!!!!!!

here's how it finally looked
Private Sub Worksheet_Calculate()
If [b9] = Sheet1.[B2] Then
[b9] = [b9]
End If
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

End Sub

thanks to everyone, especially Tom Urtis