could someone take pitty on me

[pickwa]

Hi Guys,

i want to put a status box on my spreadsheet for sending out fines so i always know from a glance what stage im at in the proceedings.

What i dont really understand is how to even start the formula.
Each condition needs to over write the previous one if you know what i mean.

If anyone could point me in the rite direction it would be greatly appreciated.

These are the individual formulas(kind of) mite give you an idea what im trying to do anyway.

=IF(J3>0, "processing","")
=IF(K3=YES,"confirmed","Send 1st Letter")
=IF(L3>"","1st letter sent","")
=IF(L3=Today()-40,"send Charge Letter","")

if it is 40 days after the date in L3?

=IF(M3>"","Charge Letter Sent,"")
=IF(M3=Today()-40,"Send Surcharge Leter","")
=IF(N3>0,"Surcharge Letter Sent","")
=IF(N3=Today()-40,"Pass On","")
=IF(O3=>"","Case Closed","")
=IF(R2="yes","Paid","")


Thanks

Pickwa

 

[Arcticwarrio]

you can only have 7 nested IF statements, you would probably need a script for that.

 

[Arcticwarrio]

and what column is this status to go in?

 

[pickwa]

Ok....not to sure what a script is to be honest wit you?

It is intended to go in the I Column.

 

[Arcticwarrio]

Right click on the sheet name at the bottom and click view code

paste this in the window that opens

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Target.Column = 10 Then
If Target.Value > 0 Then Target.Offset(0, -1).Value = "processing"
End If
If Target.Column = 11 Then
If Target.Value = "YES" Then Target.Offset(0, -2).Value = "confirmed, Send 1st Letter"
End If
If Target.Column = 12 Then
If Target.Value <> "" Then Target.Offset(0, -3).Value = "1st letter sent"
If Target.Value > Date - 40 Then Target.Offset(0, -3).Value = "send Charge Letter"
End If
If Target.Column = 13 Then
If Target.Value <> "" Then Target.Offset(0, -4).Value = "send Charge Letter"
If Target.Value > Date - 40 Then Target.Offset(0, -4).Value = "Send Surcharge Leter"
End If
If Target.Column = 14 Then
If Target.Value > "0" Then Target.Offset(0, -5).Value = "Surcharge Letter Sent"
If Target.Value > Date - 40 Then Target.Offset(0, -5).Value = "Pass On"
End If
If Target.Column = 15 Then
If Target.Value >= "0" Then Target.Offset(0, -6).Value = "Case Closed"
End If
If Target.Column = 18 Then
If Target.Value = "YES" Then Target.Offset(0, -9).Value = "Paid"
End If
End Sub

press ALT + Q

 

[pickwa]

Thanks it almost works just as i want it to.

Im going to attempt to work the bit that doesnt work out for myself if i can.........(now you have done the hard bit!)

Really appreciate the help again!

cheers

 

[pickwa]

Hi,

Really sorry to bother you again.

I understand that in excel when you put date +40 it will give you the date 40 days after today. Is that different in when talking about scripts?

If Target.Value > Date - 40 Then Target.Offset(0, -5).Value = "Pass On"

This line only actions when i put a date 40 years in the future and i need it to be 40 days in the future or 1 month would do if that makes it easier. How would i go about fixing this?

Cheers

Pickwa