formula connected to a dropdown list
 

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.

formula connected to a dropdown list
 

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.

--
Biff
Microsoft Excel MVP


"Susie" wrote in message
...
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.

formula connected to a dropdown list
 

I have 2003

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Susie" wrote in message
...
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.

formula connected to a dropdown list
 

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



--
Biff
Microsoft Excel MVP


"Susie" wrote in message
...
I have 2003

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Susie" wrote in message
...
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.

formula connected to a dropdown list
 

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

"T. Valko" wrote:

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



--
Biff
Microsoft Excel MVP


"Susie" wrote in message
...
I have 2003

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Susie" wrote in message
...
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.

formula connected to a dropdown list
 

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?

--
Biff
Microsoft Excel MVP


"Susie" wrote in message
...
I tried it but it still comes up as currency. Does the security need to be
changed?

"T. Valko" wrote:

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



--
Biff
Microsoft Excel MVP


"Susie" wrote in message
...
I have 2003

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Susie" wrote in message
...
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.

formula connected to a dropdown list
 

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" wrote:

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?

--
Biff
Microsoft Excel MVP


"Susie" wrote in message
...
I tried it but it still comes up as currency. Does the security need to be
changed?

"T. Valko" wrote:

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



--
Biff
Microsoft Excel MVP


"Susie" wrote in message
...
I have 2003

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Susie" wrote in message
...
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.

formula connected to a dropdown list
 

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.

--
Biff
Microsoft Excel MVP


"Susie" wrote in message
...
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" wrote:

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?

--
Biff
Microsoft Excel MVP


"Susie" wrote in message
...
I tried it but it still comes up as currency. Does the security need to
be
changed?

"T. Valko" wrote:

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



--
Biff
Microsoft Excel MVP


"Susie" wrote in message
...
I have 2003

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Susie" wrote in message
...
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.