formula connected to a dropdown list

S

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

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.
 
A

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
 
S

Susie

I have 2003

T. Valko said:
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

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
 
T

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?
 
S

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top