Hi Glen
Try putting the following code behind sheet 1.
Whenever a user enters a new value into column B (the job title) on sheet ,
each objective for that job title on sheet2 is filled in to the right of the
cell just changed.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Lastrow As Long, Lastcol As Integer
Lastrow = Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
Lastcol = Cells.Find(What:="*", SearchDirection:=xlPrevious,
SearchOrder:=xlByColumns).Column
If Target.Column = 2 Then
Range(Cells(Target.Row, 3), Cells(Target.Row,
Lastcol)).ClearContents
Dim i As Long, Tcol As Long
Tcol = 3
For i = 3 To Lastrow
If Target = Worksheets("Sheet2").Cells(i, 1).Text Then
Cells(Target.Row, Tcol) = Worksheets("Sheet2").Cells(i,
2).Text
Tcol = Tcol + 1
End If
Next
End If
End Sub


"glen " wrote in message
...
Hello,

Problem;
I have a vlookup set up between 2 worksheets with the possibility of 6
results. I need to record all possible results in column C under each
other. Is there a way insert rows to include all possible results using
VB code. I have tried a few other way using formulas but have come the
conclusion that VB code must be the answer. I have not dealt with a
high level of VB so a detailed response would be greatly appreciated.

Example;
I am trying set up a spreadsheet to calculate bonuses, depending on
your position there are certain objectives. In 1st sheet is a list of
employees and their positions and on the second is the position with
their objectives. I want to extract the objectives for each employee
according to their position.

SHEET 1
Collum A Collum B
John Smith Sales Manager
Greg Hobbs Branch Manager

SHEET 2
Collum A Collum B
Sales Manager Revenue
Sales Manager Income
Sales Manager America's & Pacific RAC
Branch Manager Utilisation
Branch Manager Branch Revenue
Branch Manager Pre Tax Income

Result;
SHEET 1
Column A Collum B Collum C
John Smith Sales Manager Revenue
Sales Manager Income
Sales Manager America's & Pacific
RAC
Greg Hobbs Branch Manager Utilisation
Branch Manager Branch Revenue
Branch Manager Pre Tax Income

Thanks


---
Message posted from http://www.ExcelForum.com/