Underlying Value

**seanyc** · 06-20-2012, 01:59 PM

How do I have an underlying number in a cell while displaying a name of something? For example I want a cell to display CAD and have the underlying value be the exchange rate of the canadian dollar so I can mulitply it by other cells. Thanks.

**ChemistB** · 06-20-2012, 02:35 PM

If your currency is in column A and amount in B, you can set up a table of exchange rates and name it (I called it ExRates) See attachment

And then in C2

=VLOOKUP(A2,ExRates,2,FALSE) * B2
Does that work for you?

**seanyc** · 06-20-2012, 02:53 PM

Thanks. And lets say I wanted to have every time "CAD" was in column A, for it to show a value of something in the adjacent column A2. How could I do that?

**ChemistB** · 06-20-2012, 03:22 PM

=VLOOKUP(A2,ExRates,2,FALSE)

or
=IF(ISERROR(VLOOKUP(A2,ExRates,2,FALSE)),"", VLOOKUP(A2,ExRates,2,FALSE))

You could also use VBA to input the value into B2 and have the exchange rates hardcoded into the VBA script or also use the Lookup table.

**seanyc** · 06-20-2012, 03:25 PM

How about if I wanted to say, for every time the word "CAD" was in one column, in the adjacent column it would display a specific formula I could write?

**ChemistB** · 06-20-2012, 03:36 PM

I'm starting to lose the concept of what you are trying to achieve with this. An example workbook with what and where you'd be entering and what you expect as a result might help.

This VBA code (in worksheet module) will enter the formula of your choosing into column B when CAD is entered into Col A

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Value = "CAD" Then
    Target.Offset(0, 1).Formula = "=C2*5"     'Specific formula here appears in Column B
End If
End Sub

**seanyc** · 06-20-2012, 05:51 PM

I'm having a little bit of trouble with the VBA. I have one column, F, which has abbreviations for various currencies on it. They are all spread out over 15k ish lines, so if I want to display the rate for each different currency, I'd have find each abbreviation and then copy and paste it in column G next to it. Is there an easier way where I can say "for everytime there is "X" in column F, show "Y" in the adjacent cell in column G"?