Dollars/Euros - handle multiple price fields

I have a tricky situation whereby I need to be able to allow two price
fields for the user to enter and the user will not always put in both
values:

Field 1
- US Dollars

Field 2
- Euro


Somehow need to show what the price will be in for either currency without
making too many calculation columns....

Is there a way to do this?

Thanks
Jason

Jason -


Need a little more info.

Lets Says:
Column A1 is $
Column B1 is Euro

What if both are filled in? What do you want to see and where?

You could use concatenate function.

In C1 try =CONCATENATE("$"&A1&" "&"/"&" ","?"&B1)

I put the ? in the formula because I am not sure what the Euro symbol is.

Is this what your looking for?

Kadens Dad

Better yet -

Try

In C1

=IF(AND(A1>0,B1>0),CONCATENATE("$"&A1&" "&"/"&" ","?"&B1),IF(A1>0,"$" &A1,IF(B1>0,"?" &B1," ")))


Kadens Dad

Hi Jason,

Maybe a worksheet change event would suit your requirements. Right click the
sheet tab, View code, and insert following (adapt ranges to suit):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range, cell As Range
Dim rDollars As Range, rEuros As Range
Dim xRate As Single

xRate = 1.35 ' better to get from some named cell

Set rDollars = Range("B2:B20") ' defined ranges maybe
Set rEuros = Range("C2:C20")

On Error GoTo errH
Application.EnableEvents = False

Set rng = Intersect(rDollars, Target)
If Not rng Is Nothing Then
For Each cell In rng
Cells(cell.Row, rEuros.Columns(1).Column) = cell / xRate
Next
Else
Set rng = Intersect(rEuros, Target)
If Not rng Is Nothing Then
For Each cell In rng
Cells(cell.Row, rDollars.Columns(1).Column) = cell * xRate
Next
End If
End If

Application.EnableEvents = True
Exit Sub
errH:
Resume Next
End Sub

If you define ranges for dollar & Euro prices, each in single columns with
same number of rows, modify the code like this:

Set rng = Intersect(Range("Dollars"), Target)
and similarly for Euros

Probably the code should include a more complete error handler in case user
inserts some text.

Regards,
Peter

<[email protected]> wrote in message
news:[email protected]...
> I have a tricky situation whereby I need to be able to allow two price
> fields for the user to enter and the user will not always put in both
> values:
>
> Field 1
> - US Dollars
>
> Field 2
> - Euro
>
>
> Somehow need to show what the price will be in for either currency without
> making too many calculation columns....
>
> Is there a way to do this?
>
> Thanks
> Jason
>
>