Can I format one cell so that it affects another cell?

[David]

Cells A1 (feet) and B1 (meters) are formatted with data validation so that
only specific values can be entered in those cells. Can I format A1 so that
if I select one of the specific values, B1 will automatically display the
equivalent meter value from the defined list of values?

 

[Jacob Skaria]

Try VLOOKUP() to get the values from a list.
OR
try the CONVERT function which comes along with the Analysis ToolPak add-in.

=CONVERT(A1/12,"in","cm")*100

If this post helps click Yes

 

[Lars-Åke Aspelin]

Cells A1 (feet) and B1 (meters) are formatted with data validation so that
only specific values can be entered in those cells. Can I format A1 so that
if I select one of the specific values, B1 will automatically display the
equivalent meter value from the defined list of values?

Not by formatting cells.
But you may try the following macro:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
feet_valuelist = Cells(1, "A").Validation.Formula1
feet_datavalues = Split(feet_valuelist, ",")
meter_valuelist = Cells(1, "B").Validation.Formula1
meter_datavalues = Split(meter_valuelist, ",")
If UBound(feet_datavalues) <> UBound(meter_datavalues) Then
MsgBox "different number of options can't be handled"
Exit Sub
End If
For i = 0 To UBound(feet_datavalues)
If "" & Cells(1, 1).Value = feet_datavalues(i) Then
Cells(1, "B").Value = meter_datavalues(i)
Exit Sub
End If
Next i
End If
End Sub

The number of "specific values" must be same for the data validation
in cells A1 and B1, and the values must be sorted so that equivalent
values are in the same places in the data validation lists.

Hope this helps / Lars-Åke

 

[David]

Thanks. I'll try your suggestion.

Try VLOOKUP() to get the values from a list.
OR
try the CONVERT function which comes along with the Analysis ToolPak add-in.

=CONVERT(A1/12,"in","cm")*100

If this post helps click Yes

 

[David]

Pretty complicated, but I'll try it out.

Not by formatting cells.
But you may try the following macro:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
feet_valuelist = Cells(1, "A").Validation.Formula1
feet_datavalues = Split(feet_valuelist, ",")
meter_valuelist = Cells(1, "B").Validation.Formula1
meter_datavalues = Split(meter_valuelist, ",")
If UBound(feet_datavalues) <> UBound(meter_datavalues) Then
MsgBox "different number of options can't be handled"
Exit Sub
End If
For i = 0 To UBound(feet_datavalues)
If "" & Cells(1, 1).Value = feet_datavalues(i) Then
Cells(1, "B").Value = meter_datavalues(i)
Exit Sub
End If
Next i
End If
End Sub

The number of "specific values" must be same for the data validation
in cells A1 and B1, and the values must be sorted so that equivalent
values are in the same places in the data validation lists.

Hope this helps / Lars-Ã…ke