Best way to solve my issue

[Corey ....]

I am trying to solve a speadsheet issue with formula's but i think it may be
too difficult, and i may have to do it in vba.

Not sure how to go about this though:

This is HOW my data is stored:

Sheet 1
Stores data of training modules setup for use, including the Module Title in
Column A and other data across each row for each separate Module.

Sheet 2
Stores data of training that has been carried out.
Each training completed is stored in a new row.
Starting with the Module Title in Column A,
The employee name in Column D, and
The Date Completed in Column AD.

Sheet 3
This is a spreadsheet i want to setup for a visual guide to who has/has not
been trained.

I have the Module Titles that have been entered into the Sheet1 Column A
Displayed in Column A in Sheet 3.

I have then listed ALL employees in Row 1 Starting from Column B across to
S.

So i now have a List of Module Titles down the Column A Left Hand side of
the Page and Employee Names Acrooss the Top of the Page.
I Now want to LOOKUP the data that exists in Sheet 2 and Display the Date(IF
Carried Out) in the Corresponding Cell for the Module Name and Employee's
Name in the SpreadSheet.

This is the part i am having difficulty in doing.

Basically the principal is as follows:

IF(SHEET3 MODULE TITLE = SHEET2 COLUMN A MODULE TITLE, and SHEET2 EMPLOYEE
NAME=SHEET3 EMPLOYEE NAME, and SHEET2 COMPLETED DATE <>""),SHEET3 CELL =
COMPLETED DATE,"")

So i want the DATE the training was done(Sheet2 Column AD Value) to be
displayed in Sheet3, otherwise leave the cell empty.

There are times when there WILL be a Module Title AND Employee Name in
Sheet2, but NO DATE, as this would indicate Training has ocured, but not
Completed with a Date added YET.
So in this case the Date would ONLY be displayed in Sheet3 WHEN there exists
a Date in the Sheet2.

Any pointers to carry this out the best way?

Corey

 

[Corey ....]

Solved:

Made uip 18 in total of below::

Sub Employee1()
Application.ScreenUpdating = False
Dim rngFound As Range
Dim rng2Found As Range
Dim rng3Found As Range
Dim res As Variant
On Error Resume Next
With Worksheets("SavedData").Range("D")
Set rngFound = .Find(What:=Sheet4.Range("B1").Value, After:=.Cells(1),
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False, Matchbyte:=False)
If rngFound.Value <> "" Then
With Worksheets("COMPLETED_MODULES").Range("A2:A43")
Set rng2Found = .Find(What:=rngFound.Offset(0, -3).Value,
After:=.Cells(1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False, Matchbyte:=False)
If rng2Found.Value <> "" And rngFound.Offset(0, 26).Text <> "" Then

With Worksheets("COMPLETED_MODULES").Range("B1:S1")
Set rng3Found = .Find(What:=rngFound.Value,
After:=.Cells(1), LookIn:=xlValues, LookAt:=xlWhole,
SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False,
Matchbyte:=False)
If rng3Found <> "" Then

rng2Found.Offset(0, (rng3Found.Column -
1)).Value = rngFound.Offset(0, 26).Text
Else
rng2Found.Offset(0, (rng3Found.Column -
1)).Value = ""
End If
End With
End If
End With
End If
End With
Application.ScreenUpdating = True
End Sub


Then called them together with:
Sub CompletedModules()
Application.ScreenUpdating = False
Call Employee1
Call Employee2
Call Employee3
Call Employee4
Call Employee5
Call Employee6
Call Employee7
Call Employee8
Call Employee9
Call Employee10
Call Employee11
Call Employee12
Call Employee13
Call Employee14
Call Employee15
Call Employee16
Call Employee17
Call Employee18
Sheets("COMPLETED_MODULES").Activate
Application.ScreenUpdating = True
End Sub

Possible a more efficient way to have 1 code isstead of having 18, but it
DOES work as required, and am now happy with the out come.

Corey....