Update Cell based on Cell Value Change

[markh1182]

Hi, If in cell A3 I enter A, I want cell F3 to update with a formula.
How can I do this? I believe it could be to use the Private Sub Worksheet_Change function but not sure.

Thanks, Mark

 

[GlennUK]

What do you want to happen to F3 precisely? Give as much information as possible.

 

[venkat1926]

you want a formula in F3 if you enter A in A3. let us assume the formula os
=c1*d1
c1 has entry 2 and d1 has entry 3

if you type in F3 the follwoing formula

=IF(A3="A",C1*D1,"")
what happens
if you type A in A3, F3 becomes 6(i.e result of formula C1*D1)
if you type something else in A3, F3 will be blank
is this wht you want or have I misunderstood.

 

[markh1182]

I already have something like that in, but I want to get rid of having formulas in column F to start with, and through VBA I want to be able to only input formulas when a user types in column A.

I want to get rid of the formulas been in there to start with to reduce the size of the spreadsheet.

 

[GlennUK]

What size is the spreadsheet?

 

[markh1182]

It was getting to be upwards of 20 meg, then when I wanted to run further macros to format the spreadsheet they were almost not running.

 

[venkat1926]

try this event code

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Range("F3") = ""
If Target <> Range("a1") Then Exit Sub
If Target = "A" Then Range("f3") = Range("c1") * Range("D1")
Application.EnableEvents = True
End Sub

rightlclick sheet tab and click view code. in the left handside window click the arrow and choose worksheet. on the right handside window click arrow and click change
auotmatically the first and the last lines of the above macro will come up. in betwen copy or type the code statements only;.

experiment with different entreis in Range A1 and hit enter.
try entering someother cell also.

modify to suit you.
venkat

 

[jindon]

venkat1926
Can you explain what the line of below does?

If Target <> Range("a1") Then Exit Sub

 

[jindon]

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
    'This is to limit the execution of the code only when A1 changed
   .Address(0,0) <> "A1" Then Exit Sub
    ' For Entire column A
   '.Column <> 1 Then Exit Sub
    .Value <> "A" Then Exit Sub
    Application.EnableEvents = False
    .Offset(2,5).Value = .Offset(,1) * .Offset(,2).Value '<- change to suite
    Application.EnableEvents = True
End With
End Sub

 

[venkat1926]

Jindon


I dont want to invoke the macro if I enter A in any othere cell . I thought in that case that statement will help

perhaps that is a wrong statment. apologies.
I shuold use your statements. thanks.

venkat