How To Force Other Cells To Adhere To Decimal Point of a Given Value?

[Flash2017]

Hey all,


I am looking for a way to create a sheet that will adhere to the decimal positions of a given value.


I have one cell that I put information into that is referenced in other cells to generate information for me. Sometimes I need this information to have 2 decimal positions (0.00) and other times I need it to have 3 decimal positions (0.000).


For further clarification this is what I am looking for:
If I put 0.00 into the cell, all of the other cells that use this value will then carry over 2 decimal points (0.00)
Likewise, if I put 0.000 into the cell, all of the other cells would then have 3 decimal values (0.000)


Rather than changing the formatting on these cells every time I have to switch between, is there a way to make it automatically adhere to the decimal points of the information I give?


I currently have the information rounding using the =ROUND() function and I go through and change the rounding point each time I enter a different decimal length.


Thanks

 

[Joe4]

You can use Conditional Formatting.

Say that cell A1 is the cell that you are entering "0.00" or "0.000" in (note that you will need to format cell A1 as Text so it does not drop the zeroes after the decimal).
Then you could apply the following Conditional Formatting rules to your cells:

1st Rule
Formula: =$A$1="0.00"
Format: choose Number with two decimal places

2nd Rule
Formula: =$A$1="0.000"
Format: choose Number with three decimal places

If you want to use the ROUND function, assuming the same entry in cell A1, then it would look something like:
=ROUND(value,LEN(A1)-2)
where value is the value/formula you are rounding

 

[veryamusing]

What about something like this?

Sub decimals()
    Dim cell As Range: Set cell = Selection
    Dim deps As Range: Set deps = cell.Dependents
    Dim var1 As Variant: var1 = Split(cell, ".")
    deps.NumberFormat = "0." & String(Len(var1(1)), "0")
End Sub

 

[Scott T]

What about something like this?

Sub decimals()
    Dim cell As Range: Set cell = Selection
    Dim deps As Range: Set deps = cell.Dependents
    Dim var1 As Variant: var1 = Split(cell, ".")
    deps.NumberFormat = "0." & String(Len(var1(1)), "0")
End Sub

If code is use to do this it makes more sense to make it a change event so it runs automatically when the cell is change .

 

[veryamusing]

If code is use to do this it makes more sense to make it a change event so it runs automatically when the cell is change .

Yep. Maybe not the "best" way, but a way...