If and date functions help needed please

[chriscavell]

Hi
I know this has been asked somewhere before (I was google searching earlier but cant find it now) but I have a spreadsheet which logs all the invoices my department receives and their relevant pieces of information.
One column, is the current status (ie who has the invoice to sign off and whether it has been approved or not). I want to enter a formula into the 'date sent to accounts' column so that when the current status column says 'yes' the 'date sent to accounts column' automatically enters the date (obviously I don't want this date to change once entered).

I have tried entering an IF statement including the date but cant get it to work.

Can anyone help?

Thanks

 

[VoG]

You will need code for this. In which column is Yes entered and which column should contain the date?

 

[lenze]

Hi and welcome to the board!!!

You can use a simple macro for this. Place the following in the Sheet module. RightClick the sheet tab and choose "View Code"
Say your Status Column is "A" and your date column is "B"

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub    'Column "A" Change as needed
If UCaseP(Target) = "YES" Then Cells(Target.Row, 2) = Date 'Column "B" Change as needed
End Sub

lenze

 

[chriscavell]

Hi
The yes column is number 11 and the date column is 13 (off the top of my head I dont have it in front of me at the moment)

Thanks

 

[lenze]

Make the following changes that are in red

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 11 Then Exit Sub    'Column "A" Change as needed
If UCaseP(Target) = "YES" Then Cells(Target.Row, 13) = Date 'Column "B" Change as needed
End Sub

lenze

 

[chriscavell]

Thank you, I will try that tomorrow and let you know how I get on

 

[chriscavell]

Thank you, I will try that tomorrow and let you know how I get on

Right, I've pasted the code in but now cant get my formula to work (I suspect that I'm missing something obvious)!

I'm entering =IF(K245,Yes,TODAY "-")

Help please
Thanks

 

[chriscavell]

Whilst playing with the formula I am occasionally receiving the following message on code screen 'Compile Error: Sub or function not defined'

 

[lenze]

I guess I'm a little confused as to what you require!!!
1) What is in Column "K"? How is it entered?
2) What column does the "YES" appear in?
3) What column does the Date go in?

lenze

 

[chriscavell]

Column K is the passed for approval column. This is the column which is changed to 'Yes' once an invoice has been approved.

I want the date to go into column N (this is the date when the invoice is sent for payment)

Thanks
Chris