Changing two cells from a formula

[NicoGOV]

I am trying to work out how to work out how to change one of two cells depending on if either of them has a value in it.

Its to work out either the amount of discount wanted or the % of discount wanted.

Because of this i cant put a formula in either field so i made a discount field for in between so

I wrote this to see if it would work but it was to no avail

=IF(H3,J3=(100/C3)*H3,H3=(C3/100)*J3)

h3 is discount per unit
j3 is discount %
c3 is unit sale price

Please please please help,

Any guidance would be much appreciated.

 

[NicoGOV]

This might not be too clear, i apologise.

I want to be able to put an amount (either the discount or discount percentage) in to their respective field and for the formula in a separate field to work out the other

 

[Sanjeev1976]

Welcome to the board.

You should be able to do this with the help of Worksheet Change event

 

[NicoGOV]

Thanks for the welcome

How would this be done?

Sorry, I am not that great at excel.

Any help is much appreciated!

 

[Sanjeev1976]

You can insert the below code in Sheet1 by pressing Alt-F11:

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Address = "$H$3" Then

Range("J3").Value = Range("H3").Value / Range("C3").Value

End If


End Sub

 

[NicoGOV]

Hi Sanjeev,

I have implemented this and only a bit of it works.

I am wanting to work it out so it will fill in the 'discount per unit' when i type in the 'discount % wanted' and visa versa.

I also want this to be able to be worked out for all my rows.

I have attached an image so you can see what i'm working with.

Many thanks,

Nico

 

[Sanjeev1976]

You can use the below code :

-------------------------

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Column = 8 Then

Target.Offset(0, 2) = Target.Value / Target.Offset(0, -5)

End If


If Target.Column = 10 Then

Target.Offset(0, -2) = Target.Value * Target.Offset(0, -7)

End If


End Sub

-------------------------

Sanjeev