circular reference
 

I am trying to make the following functionality:

Two cells:
1. cell A1 which in it I will write a price without VAT.
2. cell A2 which in it I will write a price with VAT.

The functionality will be:
1. If I am writing the price without VAT in A1, the price will automatically
be calculated into A2 and will be written with VAT.
2. on the contrary of step 1.

How am I doing this ?

Another problem I encountered is:
When I write in A1 the price, I am overriding the formula of the cell.
Do I have any option to define a formula for a cell that enables me to write
a number in the cell without overriding the formula that is defined to that
cell ?

Thanks, Ido

circular reference
 

I don't think that you can do what you want with formulas, I think that you
will need VBA.

Right click on the sheet tab and select View Code then copy and paste the
following code into the Sheet Module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("A:A")) Is Nothing Then GoTo ColB
Cells(Target.Row, 2).Value = _
Application.Round(Cells(Target.Row, 1).Value * 1.175, 2)
ColB:
If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
Cells(Target.Row, 1).Value = _
Application.Round(Cells(Target.Row, 2).Value / 1.175, 2)
End Sub

The Columns A & B should then do what you want - that is assuming that your
VAT rate is 17.5%

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"circular reference" <circular wrote in
message ...
I am trying to make the following functionality:

Two cells:
1. cell A1 which in it I will write a price without VAT.
2. cell A2 which in it I will write a price with VAT.

The functionality will be:
1. If I am writing the price without VAT in A1, the price will
automatically
be calculated into A2 and will be written with VAT.
2. on the contrary of step 1.

How am I doing this ?

Another problem I encountered is:
When I write in A1 the price, I am overriding the formula of the cell.
Do I have any option to define a formula for a cell that enables me to
write
a number in the cell without overriding the formula that is defined to
that
cell ?

Thanks, Ido

circular reference
 

If VBA is not the way you want to go, and you would be happy using columns A
and B for input and C and D for the 'actual' data:

In C1 enter =IF(A10,A1,ROUND(B1/(1+13%),2))
In D1 enter =IF(B10, B1,ROUND(A1*(1+13%),2))

Of course, adjust 13% to local value
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"circular reference" <circular wrote in
message ...
I am trying to make the following functionality:

Two cells:
1. cell A1 which in it I will write a price without VAT.
2. cell A2 which in it I will write a price with VAT.

The functionality will be:
1. If I am writing the price without VAT in A1, the price will
automatically
be calculated into A2 and will be written with VAT.
2. on the contrary of step 1.

How am I doing this ?

Another problem I encountered is:
When I write in A1 the price, I am overriding the formula of the cell.
Do I have any option to define a formula for a cell that enables me to
write
a number in the cell without overriding the formula that is defined to
that
cell ?

Thanks, Ido