Hey again,

Listbox3 located in Sheet("Reports")
Core data located in Sheets("Workload")

Depending on a value (staffmember) selected in Listbox1, I am looking for code that will search for this value in Sheets("Workload") - Column G

Sheets("Workload") - Row 3 has all my headings. Columns B - H are just categories while Column I onwards is week-ending dates.

Once the staff member is found in Column G of Sheets("Workload") I need the value from Columns C (Project name) added to listbox3

As for the week-ending dates, I need 6 columns of data(first column being the current week-ending)

So the end result should be 7 columns of data (1 name and 6 values)

Another twist is that there may be more than one entry for Staffmember but the Project Names are uniqe. So the code would have to list a row of name and 6 values for each Project Name found for that particular staffmember.

Here is some code I have noted down that would probably need to go in it.

[vba]
Option Explicit

Function ColumnLetter(col As Long)
Dim sColumn As String
On Error Resume Next
sColumn = Split(Columns(col).Address(, False), ":")(1)
On Error GoTo 0
ColumnLetter = sColumn
End Function


Sub Add_Info_to_Listbox3()

'This code is not any particular order'
'I'm just noting down what needs doing

'Current Week ending date
StartDate = Date + 6 - Weekday(Date)

'Find weekending value in Row 3 of Sheet("Workload")
StartCol = Application.Match(CLng(Start2Date), Worksheets("Workload").Rows(3), 0)

'Find staff member in Column G of Sheets("Workload")
temp = ListBox1.ListIndex
With ListBox1
StaffMember = .List(temp, 1) & " " & .List(temp, 0)
End With
On Error Resume Next
Rw = Sheets("Workload").Columns(7).Find(StaffMember).Row
'Code to find and select 7 columns of data here
'
'and add to listbox 3
'
'
'
'
Sheets("Reports").ListBox1.Activate
End Sub
[/vba]


Example sheet attached in case none of the above made sense