IF statement on formatted cells

[consultingexcel]

Good Afternoon Excel Experts!

I currently have a set of data that has two currencies: AUD and USD.

In this set of data i have many columns of locations and columns of pricing - the service to be provided is quoted in both AUD and USD for different locations. I would like to make this pricing all AUD in a seperate working tab, however the quoted currencies are differentiated by Currency Formatted Cells.

Question:

Does anyone know how to do an IF statement on a formatted cell i.e =IF(A1 ="USD",A1*0.96,A1) . I have tried this formula but due to the fact that the cells are formatted this formula will not work.

Anyone have any suggestions on how to get around this?

Thanks!

 

[JackDanIce]

Welcome to the board.

Don't think it's possible with the built in Excel functions to do what you want, you're likely to need a custom function. Have a read of this thread I just found:
if statement based on cell's color

 

[Kreszch68]

You can use the next vba routine to change from USD to AUD. First copy all amounts to where you want to do the conversion, then set the right range in the code and run it.

Public Sub ConvertUSDtoAUD()
'Numberformats
Const dQ As String = """"
Const sAUD As String = "_ [$AUD] * #,##0.00_ ;_ [$AUD] * -#,##0.00_ ;_ [$AUD] * " & dQ & "-" & dQ & "??_ ;_ @_ "
Const sUSD As String = "_ [$USD] * #,##0.00_ ;_ [$USD] * -#,##0.00_ ;_ [$USD] * " & dQ & "-" & dQ & "??_ ;_ @_ "
Dim r As Range
[COLOR=#ff0000][B]Set r = Sheets(2).Range("A1:A2")
[/B][/COLOR]   For Each c In r
        If c.NumberFormat = sUSD Then
            c.Value = c.Value * 0.91
            c.NumberFormat = sAUD
        End If
    Next c
End Sub