Conditional format on Drop Box

[Guest]

I have a drop down box with two selections Orders and Sales. When orders
selected, I want just a number, when sales selected, I want currency format.

Using conditional formatting does not give the option to change number format.

Any ideas?

Thanks,
Tom

 

[Gord Dibben]

And the data to format would appear where after selecting Sales or Orders from
the dropdown?

You cannot format text to Curency or Number.

You could use event code to re-format numeric cells after choosing from the
dropdown.

Post back with more details.


Gord Dibben MS Excel MVP

 

[Guest]

My table looks like the table below. The formula in each cell (here is the
formula in case it will help
-IF($A$1=2,SUMIF(Budget!$B$5:$B$80,Qtr!$A6,Budget!AB$5:AB$80)/1000,SUMIF(Budget!$B$99:$B$174,Qtr!$A6,Budget!AB$99:AB$174)/1000)
looks to the drop box in cell $A$1 to decide wheather to pull from the
orders column or the sales coulm in my data tables. When the person chooses
orders, I want the table below to change to number format, not dollars.

thanks You...

Demand (000) 2007 Budget
Qtr1 Qtr2 Qtr3 Qtr4 Total

PY Carryins $822 $0 $0 $0 $822
PY Catalogs $7,483 $6,976 $7,322 $9,154 $30,934
PY Apricots $55 $55 $55 $55 $220
PY Canada $255 $302 $212 $266 $1,034

PY Inquiries $656 $822 $713 $639 $2,829
PY Ship Kits $451 $403 $407 $485 $1,746
PY Online $1,613 $1,503 $1,378 $1,689 $6,184

PY Total $11,335 $10,060 $10,086 $12,289 $43,770
% of Year 26% 23% 23% 28% 100%

EY Carryins $242 $0 $0 $0 $242
EY Catalogs $9,594 $5,062 $5,108 $5,649 $25,413
EY Apricots $214 $159 $213 $168 $753

EY Inquiries $534 $577 $321 $464 $1,896
EY Ship Kits $385 $249 $241 $265 $1,140
EY Online $894 $856 $790 $958 $3,498

EY Total $11,862 $6,902 $6,674 $7,504 $32,941
% of Year 36% 21% 20% 23% 100%

B2B CarryIn $45 $3 $0 $0 $48
B2B Catalog $152 $188 $200 $224 $764

B2B Total $197 $191 $200 $224 $812
% of Year 24% 24% 25% 28% 100%

Total $121 $755 $184 $864 $1,924
% of Year 6% 39% 10% 45% 100%

 

[Gord Dibben]

Here is some event code that depends upon the selection in A1.

You said "Orders" and Sales" but I see by your formula that A1 could =2

Adjust the code to suit your two dropdown options.

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False

Select Case Target.Value

Case "Orders" 'or 1 or 2
For Each cell In Range("B7:F37")
If Not cell.NumberFormat = "0%" Then
cell.NumberFormat = "#,##0"
End If
Next cell

Case "Sales" 'or 1 or 2
For Each cell In Range("B7:F37") 'adjust to suit also
If Not cell.NumberFormat = "0%" Then
cell.NumberFormat = "$#,##0"
End If
Next cell

End Select
endit:
Application.EnableEvents = True
End Sub

This is worksheet event code. Right-click on the sheet tab and "View Code".

Paste the above into that sheet module.


Gord