Advice on a formula please

[xyzabc198]

Hi there,

Im pretty new to using formats on databases so I hope you can give me some simple advice, if it gets too tricky though I have a techy I can speak to.

Here's what I'm trying to do

In column AC I have a date column
And in AE I have a comments column

And in each row I have a company name, I enter the date I last called them in AC and what they said in AE.

I want a format that will make todays date appear in column AC every time I enter something into AE for that company...is this doable? any advice would be greatly appreciated!

~Jonathan!

 

[xyzabc198]

I meant using formulas, not formats :P

 

[Peter_SSs]

Welcome to the MrExcel board!

Have a look at this:
http://www.mrexcel.com/forum/showthread.php?t=309213

Edit: You can't keep the result 'fixed' if you use a formula.

 

[Kaps_mr2]

AC1= IF(AE1<>"",today(),"")

and copy down over column AC. Thanks

Kaps

 

[VoG]

Try this. Right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 31 Then
    Application.EnableEvents = False
    Target.Offset(0, -2).Value = Date
    Application.EnableEvents = True
End If
End Sub

Close the code window using the X.

 

[DonkeyOte]

Kaps, unfortunately the values in AC would change everday where AE <> "".

 

[Kaps_mr2]

Good point. Must wake up ....Give me 5 hours ...

 

[xyzabc198]

Try this. Right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 31 Then
    Application.EnableEvents = False
    Target.Offset(0, -2).Value = Date
    Application.EnableEvents = True
End If
End Sub

Close the code window using the X.

Thanks for that VoG your a legend, worked perfectly, thumbs up to you!

 

[Peter_SSs]

Thanks for that VoG your a legend, worked perfectly, thumbs up to you!

Sounds like you are satisfied but have you considered what should happen to col AC if:
1. A value is deleted from col AE?
2. Multiple values are deleted at once from col AE?
3. Multiple values are entered into col AE at once (eg by a copy and paste, or by entry via Ctrl+Enter)?

 

[xyzabc198]

Sounds like you are satisfied but have you considered what should happen to col AC if:
1. A value is deleted from col AE?
2. Multiple values are deleted at once from col AE?
3. Multiple values are entered into col AE at once (eg by a copy and paste, or by entry via Ctrl+Enter)?

1. I wouldn't be deleting anything, it is corporate data that I must be very careful with, no room for error
2. Won't be deleting
3. Every column will have different details, nothing would be C+P'd.
4. I have a copy of the original just incase

but thanks for the heads up!