formula connected to a dropdown list

[Susie]

I am setting up a expense report using drop down lists for TYPE;ie airfare,
taxi, mileage. I also have a column AMOUNT that I have designated to be in
currency form. When mileage is selected how do I have the AMOUNT cell convert
to number as opposed to currency.
Ex: When mileage is selected and I put in the AMOUNT column the number 78
it comes up as $78 and I want it to come up as just 78.

 

[T. Valko]

What version of Excel are you using?

If you're using Excel 2007 you can do this with conditional formatting. If
you're using another version then you'll need an event macro.

 

[Aqib Rizvi]

Conditional formatting provides options for Font, Border and Patterns
only so that option is not available.
Besides, as you can not add Mileage and Amounts together, you may like
to consider putting them in two separate columns.

Aqib Rizvi

 

[Susie]

I have 2003

What version of Excel are you using?

If you're using Excel 2007 you can do this with conditional formatting. If
you're using another version then you'll need an event macro.

 

[T. Valko]

Let's assume the range of cells with the drop down lists is A2:A10. The
range B2:B10 is where you enter amounts (numeric values). Range B2:B10 is
already formatted as Currency.

On the sheet where you want this to happen...

Right click the sheet tab, select View code

Copy/paste the code below into the window that opens:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo endit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A2:A10")) Is Nothing Then
If Target.Value = "Mileage" Then
Target.Offset(0, 1).NumberFormat = "General"
Else
Target.Offset(0, 1).NumberFormat = "$#,##0.00"
End If
End If
endit:
Application.EnableEvents = True
End Sub

Hit ALT Q to close the window and return to Excel.

Try it out.

If you need to change the ranges:

Range("A2:A10") = range of cells with drop down lists

Target.Offset(0, 1) = offset the current drop down list cell by 0 rows and 1
column to the right

 

[Susie]

I tried it but it still comes up as currency. Does the security need to be
changed?

 

[T. Valko]

This is why I never reply to code questions (unless I'm bored out of my
mind!)! <g>

In my test file using Excel 2002 I set macro security to High.

The procedure worked ok for me.

I suspect you may not have the correct ranges defined or maybe you put the
code in the wrong place.

Would you like me to put together a small sample file to demonstrate this?

 

[Susie]

Maybe I should get the 2007 version since it will be utlimately run on
another computer and I found out it has 2007.

 

[T. Valko]

In Excel 2007 it can be done much easier through conditional formatting.
However, if the file is ever opened on older versions of Excel then you'll
lose the conditional formatiing.