I have a data validation drop down list on sheet1 and the source of the list on sheet2. The data on sheet2 is a set of records, one per row. When a user makes a selection from the drop down I need to query the address of the cell that provided the data on sheet2. I am using the Workbook_SheetChange() function that for now just pops up a message box for testing. I want to transfer data from other columns in the list on sheet2 to sheet1 so what I need is the row reference on sheet2 of the data that was selected by the user on sheet1.
0
votes
This worked (from Craig on Excel Blackbelts): Dim oList as Range Dim lRow as Long Set oList = evaluate(Target.Validation.Formula1) lRow = Application.WorksheetFunction.Match(Target,oList,0) Thanks for looking.
– user2741671
I don't actually understand what your problem was; nothing in your “question” looked like a real question…
– Donal Fellows
1 Answers
0
votes
There is really no need to use VBA for this, a simple vlookup will suffice.
=VLOOKUP(Sheet1!A1,Sheet2!A1:C20,2,0)
Where sheet1!A1 is the data validation cell, Sheet2!A1:C20 is you data range on sheet 2 hopefully with the validation data source in column A, 2 is the column of data you want to return (2=B,3=C), and 0 ensures an exact match.
