# New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

## Interdependent cells : Currency converter

Free Excel Macros

### Interdependent cells : Currency converter

Daily practice of worksheet design requires the value of a cell being determined by that of another. So, you enter a value in one cell and a formula in the other. But what if you need to give the user the option of entering either value? The theory - and solution - may be demonstrated by a currency converter. You know the exchange rate and you would like to apply it to whatever amount the user enters in either currency to determine the amount in the other.

The attached workbook realizes this concept. However, the solution offered is by no means limited to such a simple calculation. The code has been designed to keep the two calculations, resulting from the user's choice of which cell to enter his data, distinctly separate from each other. Therefore the system is capable of handling completely different calculations. For example, if applied to a data retrieval task the user might look for an item by either entering its price in one cell or its color in another. The resulting look-ups aren't in any way related by a common exchange rate, and that is the advantage of the attached code which handles the two tasks separately.

The code comes in two parts. Drag or copy/paste the module Convert to your own project. Then paste this code to the worksheet's code module where your two cells are.
Code: Select all
`Option ExplicitPrivate Sub Worksheet_Change(ByVal Target As Range)    Dim Rng As Range        With Application        Set Rng = .Union(Range(Cone), Range(Ctwo))        If Not .Intersect(Rng, Target) Is Nothing Then            Convert Target, Rng   ', Conv        End If    End WithEnd Sub`

At the top of the code in the module Convert there are two constants defining the two inter-related cells.
Code: Select all
`    Public Const Cone As String = "C6"    Public Const Ctwo As String = "D6"`
In the example they are adjecent to each other, but you can change these addresses to be anywhere on one worksheet. Also, in the example, the exchange rate is supplied from a cell in the same worksheet. The code doesn't require any input from the worksheet. When you create the formulas that determine the value of the opposite cell you are entirely free to use data from any source including data stored in the code itself.
You do not have the required permissions to view the files attached to this post.
Have a great day!

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator

Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010