Insert Value Based upon Entry into Another Cell

[LauraEdson10]

Hi Everyone,

I'm sure this is fairly easy but I just can't figure it out.

Information will be entered into Sheet 1, cell E5 which is the mileage from our business to the customer's business. Down in Sheet 1 cell B76, I'm wanting this to then calculate a mileage charge based upon the information entered into E5. Our structure is as follows:

0-2 Miles - Free of Charge.
3-15 Miles - £85
16-25 Miles - £130
26-40 Miles - £140
41-50 Miles - £150
51-70 Miles - £215
71-100 Miles - £265

If there a formula out there to automatically calculate this please? Thank you.

 

[mumps]

It can be done with a formula, but I suggest you try the following: Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a value in E5 and exit the cell. Cell B76 will populate automatically.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("E5")) Is Nothing Then Exit Sub
    Select Case Target.Value
        Case Is = 0, Is < 3
            Range("B76") = "Free of Charge"
        Case Is = 3, Is < 16
            Range("B76") = 85
        Case Is = 16, Is < 26
            Range("B76") = 130
        Case Is = 26, Is < 41
            Range("B76") = 140
        Case Is = 41, Is < 51
            Range("B76") = 150
        Case Is = 51, Is < 71
            Range("B76") = 215
        Case Is = 71, Is < 101
            Range("B76") = 265
    End Select
End Sub

 

[reberryjr]

Here is a formula that you can use. Just put it in cell B76, and continue the pattern.

=IF(E5<3,"Free of Charge",IF(E5<16,"85",IF(E5<26,"130")))

One question though...are you able to add a table to your spreadsheet for flexibility, in the event that the mileage range and/or associated mileage charge is changed? If you are able to do that, or if it already exists, you're able to direct the formula to that table, so you don't have to consistently change the formula directly.

[TABLE="class: grid, width: 103"]
<tbody>[TR]
[TD]Start
[/TD]
[TD]End
[/TD]
[TD]Fee
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2
[/TD]
[TD]£0
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]15
[/TD]
[TD]£85
[/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD]25
[/TD]
[TD]£130
[/TD]
[/TR]
[TR]
[TD]26
[/TD]
[TD]40
[/TD]
[TD]£140
[/TD]
[/TR]
[TR]
[TD]41
[/TD]
[TD]50
[/TD]
[TD]£150
[/TD]
[/TR]
[TR]
[TD]51
[/TD]
[TD]70
[/TD]
[TD]£215
[/TD]
[/TR]
[TR]
[TD]71
[/TD]
[TD]100
[/TD]
[TD]£265
[/TD]
[/TR]
</tbody>[/TABLE]

 

[LauraEdson10]

It's worked perfectly, thank you!

 

[reberryjr]

Glad I could help.