To do what you want you would need VBA. Right-click on the sheet tab and
select View Code then enter this Macro:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VAT As Double
VAT = 0.175
If Intersect(Target, Range("F8:F10")) Is Nothing Then Exit Sub
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
If Target.Row = 8 Then
Cells(9, 6).Value = Application.Round(Target.Value * VAT, 2)
Cells(10, 6).Value = Target.Value + Cells(9, 6).Value
End If
If Target.Row = 9 Then
Cells(8, 6).Value = Application.Round(Target.Value / VAT, 2)
Cells(10, 6).Value = Target.Value + Cells(8, 6).Value
End If
If Target.Row = 10 Then
Cells(8, 6).Value = Application.Round(Target.Value / (1 + VAT), 2)
Cells(9, 6).Value = Target.Value - Cells(8, 6).Value
End If
Application.EnableEvents = True
End Sub
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
Replace @mailinator.com with @tiscali.co.uk
"Tina Harrison" wrote in message
...
I don't think i explained myself very well.
I need to enter data in either F9 or H9
so if i enter data in F9 the answer will be in H9 and vise versa
and then maybe enter a formula in G10 to show the VAT.
Can this be done?
Thank you for your help
Tina
"Peo Sjoblom" wrote:
You can't have a function in a cell then enter something in that same
cell
So if you create a 4th and 5th column
to get the Net if you enter something in the total use
=F10/(1+17.5%)
to get the VAT
=F10-(F10/(1+17.5%))
If you put in the Net in F8
then use
=F8*17.5%
to get the VAT
and
=F8*(1+17.5%)
to get the total
--
Regards,
Peo Sjoblom
"Tina Harrison" wrote in message
...
Hi all
I have a worksheet like below, It might be easy for some but i'm a bit
rusty.
F8 F9 F10
Net VAT Total
What i need is a function that will allow me to enter a amount in
either
Net
or Total and it would work out Vat and either Net or Total depending
where
i
have put the amount. Hope this makes sense!
Many thanks
Tina