Automatically chnage number format of a range of cells

[Tonso]

I have a range of cells in a worksheet, starting at cell G21,that I enter data into. Depending on circumstances, I sometimes need to enter the data aswhole numubers, or as decimal numbers with 2, 3, or 5 decimals. Is there away i can have a data validation dropdown list that will allow me to choose how the entire range (G21:G200) will be formatted? For example, Dept A would require the data to be entered and displayed as whole numbers, Dept B would reguire 2 decimal places, such as 0.55, or 12.63, Dept C would require3 decimals, and Dept D would require 5 decimals. This same formatting would also need to be applied to cells C12:F15, where data is summarized. So the dropdown list would consist of Dept A, Dept B, Dept C, Dept D.

Thanks,
Tonso

 

[joeu2004]

I have a range of cells in a worksheet, starting at cell
G21,that I enter data into. Depending on circumstances,
I sometimes need to enter the data as whole numubers, or
as decimal numbers with 2, 3, or 5 decimals. Is there a
way i can have a data validation dropdown list that will
allow me to choose how the entire range (G21:G200) will
be formatted?

Not a drop-down list. But you can use Data Validation, which you would
apply to the individual ranges.

Select the range, for example G21:G200.

Click on Data, then Validation or Data Validation (depending Excel version),
then the Settings tab.

Click on the Allow drop-down list.

For whole numbers, select Whole Numbers. Fill in the Minimum and Maximum
fields. If you want to all all whole numbers, you can use -1E300 and 1E300
respectively.

(Those are just easy-to-remember limits. The minimum and maximum whole
numbers are actually somewhat larger. See the "specifications and limits"
help page. But -1E300 and 1E300 will probably cover your needs.)

To require 2 decimal places, for example, select Custom and enter the
following formula:

=IF(AND(ISNUMBER(G1),ISNUMBER(FIND(".",G1))),LEN(G1)-FIND(".",G1)=2,FALSE)

Change 2 to 3 or 5 or whatever.

You can also click on the Input Message and Error Alert tabs to customize
the behavior of the validation.

 

[lhkittle]

I have a range of cells in a worksheet, starting at cell G21,that I enterdata into. Depending on circumstances, I sometimes need to enter the data as whole numubers, or as decimal numbers with 2, 3, or 5 decimals. Is therea way i can have a data validation dropdown list that will allow me to choose how the entire range (G21:G200) will be formatted? For example, Dept A would require the data to be entered and displayed as whole numbers, Dept Bwould reguire 2 decimal places, such as 0.55, or 12.63, Dept C would require 3 decimals, and Dept D would require 5 decimals. This same formatting would also need to be applied to cells C12:F15, where data is summarized. So the dropdown list would consist of Dept A, Dept B, Dept C, Dept D.



Thanks,

Tonso

Hi Tonso,
Try this code with your drop down list of Dept's in cell A1.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing _
Or Target.Cells.Count > 1 Then Exit Sub

If Target.Value = "Dept A" Then
Range("G21:G200", "C12:F15").NumberFormat = "0"
ElseIf Target.Value = "Dept B" Then
Range("G21:G200", "C12:F15").NumberFormat = "0.00"
ElseIf Target.Value = "Dept C" Then
Range("G21:G200", "C12:F15").NumberFormat = "0.000"
ElseIf Target.Value = "Dept D" Then
Range("G21:G200", "C12:F15").NumberFormat = "0.00000"
End If

End Sub

HTH
Regards,
Howard