What if question

[leoj823]

I need a formula to do the following. When I enter an x in f26 I need the figure in e26 to double. The name of the page is mileage form.

 

[jim may]

Paste this code into your Sheet module...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$F$26" Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
Range("E26").Value = Range("E26").Value * 2
End Sub

 

[leoj823]

Hi,

Thank you for your prompt response. I'm afraid I don't know that much about Macros. Is there a formula that I could use for that cell and then drag it down to the cells below. I need to go from F26 to F47 to correspond to E26 to E47.

Leo

 

[leoj823]

Hi,

Thank you for your prompt response. I'm afraid I don't know that much about Macros. Is there a formula that I could use for that cell and then drag it down to the cells below. I need to go from F26 to F47 to correspond to E26 to E47.

Leo

 

[gsbelbin]

Jim doesn't appear to be on line at the moment so I'll have a go at answering this. No you cannot enter a formula which would do this, it will need to use the Worksheet_Change event as Jim has shown. To include all the other cells that you have identified you could use the following adaptation of Jim's code.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Range(Target.Address), Range("F26:F47")) Is Nothing Then
        Exit Sub
    Else
        If Target.Cells.Count > 1 Then
            Exit Sub
        Else
            Range("E" & Target.Row).Value = Range("E" & Target.Row).Value * 2
        End If
    End If
End Sub

 

[scottylad2]

I need a formula to do the following. When I enter an x in f26 I need the figure in e26 to double. The name of the page is mileage form.

Where will the figure in E26 be drawn from? if you're manually entering it then your only solution is a VBA, like the one posted. If it's the result of a formula, then you could wrap that formula in an IF along the lines of =IF(F26="x",YourFormula*2,YourFormula)

An alternative if it's a manual entry would be for you to enter what would be going into E26 somewhere else, say for instance A26 as an example, then you could use a formula in E26 that says =IF(F26="x",A26*2,A26)

 

[Peter_SSs]

Duplicate to http://www.mrexcel.com/forum/showthread.php?t=546376

Please do not post the same question multiple times. Questions of a duplicate nature will be locked or deleted, per the Posting Rules (especially points 21-23).

Any bumps, clarifications, or follow-ups should be posted back to the linked thread.