Changing Number format using an If Statement/Formula?

[nwsportsreport25]

Hey all,

Is there a way to change the number format of a cell using an if statement. For example, if I am creating a drop-down menu that includes two metrics in two different number formats, is there a way to toggle between the number formats within the same cell depending on the category I use in the drop-down? Is there an alternate formula I can use? Since I am not proficient with VBA, I am trying to stay away from this for now.

i.e.

Cell B1 is variable:
Drop Down Menu:
1 - Net Sales
2 - % of Sales

A1: West Region
B1: Net Sales: $30,000
B2: % of Total Sales: 25%

If I select 1 - Net Sales, it will populate with 30,000, keeping A1 West Region fixed. If I select 2 - % of Sales, it will populate with 25%, keeping A1 West Region fixed. However, currently, the 25% defaults to the same number format I am using for 1 - Net Sales, so it populates as $0.

Does anyone know if a quick, simple formula to toggle between two different number formats?

Thanks

 

[lenze]

Hi and welcome to the Board!!! You will need a SIMPLE code!!! I can't, however, figure out your layout. What cell contains the Dropdown and which cell(s) do you want the formatting to apply?
lenze

 

[nwsportsreport25]

Thanks lenze for such a prompt response. I apologize for not including more detail. I'll try my best to explain this, but may need to provide a screen shot.

A1: Region
A2: West Region
B1: Metric
B2: Variable (Where I want to insert the metric)

Drop down - Form Control index
E1: 1 (Net Sales)
E2: 2 (of Sales)

A4: Region
A5: West Region
B4: Net Sales
B5: 30,000
C4: % of Sales
C5: 35%

Region: Metric:
West Region x

Looking for B2 to populate with either B5 or C5 depending on the metric I choose from the form control box.


Thank you!

 

[lenze]

OK: I'm still a little confused. Are you using a dropdown via Data Validation? If so, what cell is it in? E1? Assuming so

Private Sub WorkSheet_Change(ByVal Target as Range)
If Target.Address <> "$E$1" Then Exit Sub
Select Case Target
    Case "Net Sales"
          With Range("$B$2")
              .Value = Range("$B$5")
              .Style = "Currency"
          End With
    Case "% of Sales"
          With Range("$B$2")
              .Value = Range("$C$5")
              .Style = "Percent"
          End With
      Case Else:
          Range("$B$2).ClearContents
          Range("B$2").Style = "Normal"
End Select
End Sub

This goes in the WorkSheet module. RightClick the Sheet Tab and choose "View Code"
Totally untested!!!
lenze