circumvent circular reference

[thadson]

Can I circumvent circular references in excel by using VBA code (or is there a better way to make a formula) to calculate currency?
In this case I have 2 columns where one is USD the other is GBP.
When I enter an amount into the USD column I want the GBP column calculate on the fixed exchange rate I placed into a separate cell (C1).
In cell B1 the formula is =IF(A1>0,(A1*$C$1),0)
In cell A1 the formula is =IF(B1>0,(B1/$C$1),0)
Cell C1 is a fixed value, like 0.639
What I want is to be able to enter either of the currencies and the other cell would calculate itself.

 

[Zakkaroo]

I'm not entirely sure what you're hoping to achieve ... the way you have your formulas set up in Cell A1 & B1 means that both cells will only ever be 0 ... even if you ignore the circular reference. You've also said that Column I is where you'll enter a USD figure and that you want the 'BGP column' to calculate on the fixed exchange rate, but you haven't told us which column should have the GBP value in it?

This might not be right, but it's based on my interpretation of what you're trying to do.

Column i = where you might type a USD figure
Column h = where you might type a GBP figure

Column a = where you want a GBP figure to appear IF there is a USD figure in Col i
A1 =IF(I1>0,(I1/$C$1),0)

Column b = where you want a USD figure to appear IF there is a GBP figure in Col h
B1 =IF(H1>0,(H1*$C$1),0)

 

[thadson]

Zakkaroo, thank you for the help.
This is what I'm trying to achieve:
Sometimes I get my price in GBP, sometimes I get it in USD.
When I have a USD price, I want to enter it manually into A column and have B column update itself with the correct GBP.
Similarly, when I enter a GBP manually into Column B I want Column A to update automatically.
I want a formula in place for both columns and I would overwrite the one with the manual data and the other side would calculate.
This is why I tried to make the cell be 0 if there is no data entered, hence my "if cell greater than 0 do formula, otherwise stay 0" statement.
It would be only a circular reference when there is no data yet in the cell, only the formula on both sides. There is only 1 formula as soon as data is entered into one of the cells.
I always need to know price in both currency, but usually I only have it in one and need to calculate the other.
Is there a way to solve this with only 2 columns, The spreadsheet is already very long and 4 column would be too much in this case.
Also there would be 2 wasted cell on every line depending on if the USD or GBP was calculated.

 

[Zakkaroo]

Ok, bear in mind that I'm not the most elegant of VBA types ... try this out

You just need to put it in the code for the sheet where you want this all to happen.

I gave it a quick try and it seemed to work for me ... it WILL fire everytime you make any change to anything in column A or column B

Private Sub Worksheet_Change(ByVal Target As Range)
sCol = Target.Column
sRow = Target.Row
sAddr = Target.Address(False, False)
Application.EnableEvents = False
If sCol = 1 Then
    Range(sAddr).Offset(0, 1).Formula = "=IF(A" & sRow & ">0,(A" & sRow & "*$C$1),0)"
ElseIf sCol = 2 Then
    Range(sAddr).Offset(0, -1).Formula = "=IF(B" & sRow & ">0,(B" & sRow & "/$C$1),0)"
End If
Application.EnableEvents = True
End Sub

 

[thadson]

Thank you, I will play with this. I appreciate the help.