Update Cell based on Cell Value Change

markh1182

New Member
Joined
Apr 25, 2006
Messages
48
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
What do you want to happen to F3 precisely? Give as much information as possible.
 

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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

New Member
Joined
Apr 25, 2006
Messages
48
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

Well-known Member
Joined
Jul 8, 2002
Messages
11,547

ADVERTISEMENT

What size is the spreadsheet?
 

markh1182

New Member
Joined
Apr 25, 2006
Messages
48
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

Well-known Member
Joined
Aug 21, 2005
Messages
4,824

ADVERTISEMENT

try this event code
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
venkat1926
Can you explain what the line of below does?
Code:
If Target <> Range("a1") Then Exit Sub
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Code:
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

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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
 

Forum statistics

Threads
1,141,096
Messages
5,704,312
Members
421,338
Latest member
Pepess

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
Top