Multiple If statement

Corleone

Well-known Member
Joined
Feb 2, 2003
Messages
711
Hopefully someone can help

spreadsheet contains 300 rows of data

In some cases the contractor who fills in the spreadsheet will fill in column j with "y" TO Indicate a particular task is done, but will then forget to put in the Actual completion dates

so I would if possible like the following to happen..........

If column J contains "Y" and the Cell in column H or I contain "TBD" - I would like
the Cells in H & I to be populated with "Date Required"

If possible i would like this to be as a worksheet event as the relevant cells already contain formulas/validation

Thanks
 
Last edited:

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,896
Then you would need a coded solution, as the data validation action would overwrite a formula
 

Corleone

Well-known Member
Joined
Feb 2, 2003
Messages
711

ADVERTISEMENT

Then you would need a coded solution, as the data validation action would overwrite a formula
Can anyone offer a coded solution?



forgot to mention ""Date Required" is one of the options in the data validation drop down
 
Last edited:

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,896
Something along the lines of

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 10 And Target.Value = "y" Then
Target.Offset(0, -2).Value = "Date Required"
Target.Offset(0, -1).Value = "Date Required"
End If


End Sub
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690

ADVERTISEMENT

Not really sure what you need, but this should get you started!!
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 10 Then Exit Sub
If UCase(Target) = "Y" Then
    With Cells(Target.Row, 8).Resize(1, 2)
       .Value = "Date Required"
    End With
End If
End Sub
lenze
 

Corleone

Well-known Member
Joined
Feb 2, 2003
Messages
711
Not really sure what you need, but this should get you started!!
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 10 Then Exit Sub
If UCase(Target) = "Y" Then
    With Cells(Target.Row, 8).Resize(1, 2)
       .Value = "Date Required"
    End With
End If
End Sub
lenze
Thanks Guys for the help - using the above code i just need on other tweak!
, if either of the cells in column H or I already have dates in them, i would like them to remain unchanged. and only for it to change the cells with "TBD" in them

thanks Again
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 10 Then Exit Sub
If UCase(Target) = "Y" Then
    If Not IsDate(Cells(Target.Row, 8)) Then Cells(Target.Row, 8) = "Date Required"
    If Not IsDate(Cells(Target.Row, 9)) Then Cells(Target.Row, 9) = "Date Required"
End If
End Sub
lenze
 

Corleone

Well-known Member
Joined
Feb 2, 2003
Messages
711
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 10 Then Exit Sub
If UCase(Target) = "Y" Then
    If Not IsDate(Cells(Target.Row, 8)) Then Cells(Target.Row, 8) = "Date Required"
    If Not IsDate(Cells(Target.Row, 9)) Then Cells(Target.Row, 9) = "Date Required"
End If
End Sub
lenze
Many Thanks Lenze, Just what i was looking for
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,209
Messages
5,509,846
Members
408,757
Latest member
Jamarr123

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top