You can certainly do this using VBA, though not as a format.

One way:

Select all the cells you want to convert and name them, say
"convert_cells". Assume the "external switch" is a validated cell (named
say, "switch") that contains either "mm" or "in":

Public Sub ToggleConversion()
Dim rArea As Range
Dim rCell As Range
Dim dFactor As Double
dFactor = 25.4 ^ (-1 - 2 * (Range("switch").Value = "mm"))
For Each rArea In Range("convert_cells").Areas
For Each rCell In rArea
rCell.Value = rCell.Value * dFactor
Next rCell
Next rArea
End Sub




In article ,
pH7 wrote:

What I want to do is have numbers entered in cells that represent
lengths in milimeters. I want to have a custom format that displays the
numbers in either milimeters or inches depending on an external switch.