Multiple If statement

Corleone

Well-known Member
Joined
Feb 2, 2003
Messages
772
Office Version
  1. 365
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 fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Corleone

Well-known Member
Joined
Feb 2, 2003
Messages
772
Office Version
  1. 365
From a dropdown list using data validation



cheers
 

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,899
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
772
Office Version
  1. 365
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,899
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
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
772
Office Version
  1. 365
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
772
Office Version
  1. 365
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
 

Forum statistics

Threads
1,176,714
Messages
5,904,596
Members
435,100
Latest member
Ginger 1775

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