MATCH OFFSET LIST VALIDATION

[Kenny]

Okay I have looked over contextures for how to do this. I know the answer is
there, but I cant figure it out, will someone please show me how to do this
without VB code. Please!

I want to use match and the offset command, I want to name a range of data
and pop it into a list validation cell.

Sheet1 will have a list validation box referencing a range name

Sheet2 has two columns of data
ColumnA ColumnB
TV 1.0
Camera 2.0

I want the list validation box to show column a, and then when I choose tv
it pops column's b value in the cell. Please help me!

 

[Kenny]

Thanks papou, but this is not what I am needing. I need the info in one cell.
In your example below there should be no d3. I want d2 to contain the list
drop down, when you click on the drop down it will show colA, but when I
click on a choice it will populate d2 with the data in colB. The drop down
shows colA, but then populates colB in same cell d2. Please help thanks

 

[papou]

Kenny
This is going to be a little tricky and will require a bit of vba
programming.
With the sample code below, once you select your choice from the validation
list in D2, the corresponding value from column B will show in D2.
Please note you will need to manually clear the value in D2 (from the column
B list) to make the validation list (from column A) available again.

Right-Click on the sheet tab, select View Code, paste the sample code below
and amend accordingly with the exact range addresses used for your data:
'(Tested on Excel 2003 SP3 WinXp Pro SP2)
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D2")) Is Nothing Then
Application.EnableEvents = False
On Error Resume Next
dummy = Target.Validation.Type
If Err <> 0 Then
Err.Clear: On Error GoTo 0

With Target.Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$A$1:$A$2"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Else
Target.Validation.Delete
Target.Value = Evaluate("=INDEX(B1:B2,MATCH(D2,A1:A2,0))")
End If
Application.EnableEvents = True
End If

End Sub

HTH
Cordially
Pascal