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?

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
---------------
Jacob Skaria


"David" wrote:

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?

On Thu, 16 Jul 2009 06:20:10 -0700, David
wrote:

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

Thanks. I'll try your suggestion.

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"David" wrote:

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?

Pretty complicated, but I'll try it out.

"Lars-Ã…ke Aspelin" wrote:

On Thu, 16 Jul 2009 06:20:10 -0700, David
wrote:

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