Format change when user select a Factor on drop down box
 

I have a drop down box with many Factors. when the user select one factor
below I like the data to show up as correct format on the out put .
How can i do it ?
Drop down box 1- Merchandise $
2- Gas $
3- Merchandise margin %
4- Gas margin %

on cell A4 I format as Currency.




--
thanks

Format change when user select a Factor on drop down box
 

liem,

Try this.

Copy the code below, right-click the cheet tab, select "View Code" and paste the code in the window
that appears.

The code is written for your dropdown being in Cell A3. Change that to the correct cell, with the $s
as shown.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$3" Then Exit Sub
If InStr(Target.Value, "$") 0 Then
Range("A4").NumberFormat = "$0.00"
Else
Range("A4").NumberFormat = "0.00%"
End If
End Sub



"liem" wrote in message
...
I have a drop down box with many Factors. when the user select one factor
below I like the data to show up as correct format on the out put .
How can i do it ?
Drop down box 1- Merchandise $
2- Gas $
3- Merchandise margin %
4- Gas margin %

on cell A4 I format as Currency.




--
thanks

Format change when user select a Factor on drop down box
 

Bernie,
I made a mistake when I asked the question last night.
On the drop down comb box the user can selected these factors

Merchandise sale
Merchandise Gross profit.
merchandise margin
Gasoline sale
Gasoline Gallon
Gasoline Margin
Labor staff

If the user selected Merchandise sale on the drop down comb box ,then the
cell A4 will show up the name Merchandise Sales. From this I used LookUp
Function to get the result show up from Cell A6:D9
example 1
Merchandise Sales
Salesman APSD Chg to PY Chg to PY %
John Robert $4509 $345 3.45%
Tom Van $7863 $349 6.87%
Linda evan $6523 $239 7.45 %
Mike Boyd $4598 $123 3.23%

If the user selected Merchandise Margin then the cell A4 will show up the
name Merchandise Margin then the out put example #2
Merchandise Margin
Salesman APSD Chg to PY Chg to PY %
John Robert 34.87 % .35% 5.45%
Tom Van 36.78% 1.11% 3.45 %
Linda evan 37.98% .45% 2.33%
Mike Boyd 34.58% .78% 4.56%

if the user select Gasoline Gallon then the out put will be
Example #3
Gasoline Gallon
Salesman APSD Chg to PY Chg to PY %
John Robert 6798 456 3.145%
Tom Van 3459 234 1.23 %
Linda evan 7234 236 2.33%
Mike Boyd 5461 120 3.56%

If the user selected merchandise gross profit or labor then the result
should be the same as example#1.
I tried your Marco but I can not get it done. Please show me how make it work

thanks
Liem
--
thanks


"Bernie Deitrick" wrote:

liem,

Try this.

Copy the code below, right-click the cheet tab, select "View Code" and paste the code in the window
that appears.

The code is written for your dropdown being in Cell A3. Change that to the correct cell, with the $s
as shown.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$3" Then Exit Sub
If InStr(Target.Value, "$") 0 Then
Range("A4").NumberFormat = "$0.00"
Else
Range("A4").NumberFormat = "0.00%"
End If
End Sub



"liem" wrote in message
...
I have a drop down box with many Factors. when the user select one factor
below I like the data to show up as correct format on the out put .
How can i do it ?
Drop down box 1- Merchandise $
2- Gas $
3- Merchandise margin %
4- Gas margin %

on cell A4 I format as Currency.




--
thanks

Format change when user select a Factor on drop down box
 

Liem,

Try this version:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$4" Then Exit Sub
If InStr(Target.Value, "margin") 0 Then
Range("B6:C9").NumberFormat = "0.00%"
ElseIf InStr(Target.Value, "gallon") 0 Then
Range("B6:C9").NumberFormat = "0"
Else
Range("B6:C9").NumberFormat = "$0.00"
End If
End Sub

HTH,
Bernie
MS Excel MVP


"liem" wrote in message
...
Bernie,
I made a mistake when I asked the question last night.
On the drop down comb box the user can selected these factors

Merchandise sale
Merchandise Gross profit.
merchandise margin
Gasoline sale
Gasoline Gallon
Gasoline Margin
Labor staff

If the user selected Merchandise sale on the drop down comb box ,then the
cell A4 will show up the name Merchandise Sales. From this I used LookUp
Function to get the result show up from Cell A6:D9
example 1
Merchandise Sales
Salesman APSD Chg to PY Chg to PY %
John Robert $4509 $345 3.45%
Tom Van $7863 $349 6.87%
Linda evan $6523 $239 7.45 %
Mike Boyd $4598 $123 3.23%

If the user selected Merchandise Margin then the cell A4 will show up the
name Merchandise Margin then the out put example #2
Merchandise Margin
Salesman APSD Chg to PY Chg to PY %
John Robert 34.87 % .35% 5.45%
Tom Van 36.78% 1.11% 3.45 %
Linda evan 37.98% .45% 2.33%
Mike Boyd 34.58% .78% 4.56%

if the user select Gasoline Gallon then the out put will be
Example #3
Gasoline Gallon
Salesman APSD Chg to PY Chg to PY %
John Robert 6798 456 3.145%
Tom Van 3459 234 1.23 %
Linda evan 7234 236 2.33%
Mike Boyd 5461 120 3.56%

If the user selected merchandise gross profit or labor then the result
should be the same as example#1.
I tried your Marco but I can not get it done. Please show me how make it work

thanks
Liem
--
thanks


"Bernie Deitrick" wrote:

liem,

Try this.

Copy the code below, right-click the cheet tab, select "View Code" and paste the code in the
window
that appears.

The code is written for your dropdown being in Cell A3. Change that to the correct cell, with the
$s
as shown.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$A$3" Then Exit Sub
If InStr(Target.Value, "$") 0 Then
Range("A4").NumberFormat = "$0.00"
Else
Range("A4").NumberFormat = "0.00%"
End If
End Sub



"liem" wrote in message
...
I have a drop down box with many Factors. when the user select one factor
below I like the data to show up as correct format on the out put .
How can i do it ?
Drop down box 1- Merchandise $
2- Gas $
3- Merchandise margin %
4- Gas margin %

on cell A4 I format as Currency.




--
thanks