Complex Data Validation

[Guest]

I am trying to perform a strange and complex data Validation on a doc. It
will be between three different sheets. What I need is depending on if a
column on the first sheet says "Complete" I need a warning to pop up, when on
the other two sheets which also have the same corresponding projects, when I
try to enter hours for that project that has "Complete" in the first page.
So I have three sheets with the same projects listed but the first page it a
total of hours and status (complete, in work, canceled) then the second sheet
has hours each week for each project, and finally the final sheet has team
members hours for each week and project they worked on. So for the second
page I need an error to pop up when I try to enter hours for this week on a
completed project and for sheet three I need and error to pop up when I type
in the project code of a completed project.

 

[Guest]

Try this:-

Asumptions:

1. Sheets are Sheets 1 to 3
2. Project ID is in Column A of ALL sheets
3. "Completed" is column B of Sheet1

Adjust code below as required.

To insert code, right click on tabof sheet2, select "View code" and copy/
paste code below.

Repeat for sheet3.

HTH


Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit
Application.EnableEvents = False
project = Cells(Target.Row, "A") '<=== Project ID in column A
res = Application.Match(project, Worksheets("sheet1").Range("A:A"), 0)
If Not IsError(res) Then
If Worksheets("sheet1").Range("B" & res) = "Completed" then
MsgBox "This project is completed: no data entry allowed"
Target.Value = "" '<== Reset input to blank
End If
End If

ws_exit:
Application.EnableEvents = True

End Sub