Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional format on Drop Box
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional format on Drop Box
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!A B$5:AB$80)/1000,SUMIF(Budget!$B$99:$B$174,Qtr!$A6,Budget!AB$9 9: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" wrote: 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 On Tue, 5 Jun 2007 08:26:02 -0700, tojo107 wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional format on Drop Box
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 On Tue, 5 Jun 2007 11:14:02 -0700, tojo107 wrote: 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!A B$5:AB$80)/1000,SUMIF(Budget!$B$99:$B$174,Qtr!$A6,Budget!AB$9 9: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" wrote: 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 On Tue, 5 Jun 2007 08:26:02 -0700, tojo107 wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional drop-downs | Excel Discussion (Misc queries) | |||
How to create a conditional format that changes the number format | Excel Discussion (Misc queries) | |||
Conditional Format - Format Transfer To Chart | Excel Discussion (Misc queries) | |||
copy conditional format to regular format | Setting up and Configuration of Excel | |||
copy conditional format as ACTUAL format | Excel Discussion (Misc queries) |