need formula to work once

ercstric

New Member
Joined
Feb 24, 2002
Messages
16
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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
 
Upvote 0
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
--
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top