Multiple If statement

Corleone

Well-known Member
Joined
Feb 2, 2003
Messages
721
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:

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

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
721

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
721
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
721
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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,714
Messages
5,660,424
Members
418,580
Latest member
JP82

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top