Automatically dated cells

[SwimmingNathan]

Hiya,

I've posted about this problem before, without much luck finding a simple (or working) answer.

I want a cell to display the current date when another cell has something entered into it.

Example: I enter @ into A1, I want B1 to display the date without me having to either click on it and enter =TODAY or enter the date manually.

There must be another formula that can be used with an IF function to say whether or not the cell is empty.

I feel that if I could get a Y/N response on whether there is anything in A1 then combining the IF and TODAY will be a doddle.

Excel 2013

Please don't mention change tracking.

Any help is greatly appreciated.

Cheers,

Swimming

 

[mumps]

The macro and the formula will always update B2. Run this macro whenever you want to change the date in B2:

Sub InsertDate()
    If Range("A1") <> "" Then Range("B2") = Date
End Sub

 

[SwimmingNathan]

The macro and the formula will always update B2. Run this macro whenever you want to change the date in B2:

Sub InsertDate()
    If Range("A1") <> "" Then Range("B2") = Date
End Sub

Sorry, I didn't explain very well before, I want the date to stay the same once a value is entered into A1. At present some cells in B have been updating without any changes being made to their corresponding cells in column A.

Hope that makes sense?

 

[mumps]

This macro, when run, affects only cell B2. Do you have formulas in the other cellls in column B? If you do, the formulas may be causing the unwanted changes.

 

[SwimmingNathan]

This macro, when run, affects only cell B2. Do you have formulas in the other cellls in column B? If you do, the formulas may be causing the unwanted changes.

There are formulas in the adjacent cells, but these don't affect B2. Emails are entered in column A, Col B gives the date they are entered into col. A, col. C checks the email entered into col. A isn't already in the database. So confused

 

[mumps]

I think that it would be much easier to follow if we could see how your data is organized. Perhaps you could upload a copy of your file to a free site such as www.box.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets.

 

[SwimmingNathan]

I've purged all the data and added notes: https://www.dropbox.com/s/ls13at3hvsk52nf/Contacts Pending Sorting Macro Date.xlsm

 

[mumps]

First of all, just to clarify, we're not taling about columns A, B and C. We're talking about columns M, N and O. You have a Worksheet_Change macro for the worksheet, that enters the date in column M when you populate column N. It seems to be working as it should. Column N changes only when you enter a value in clumn M. I don't see any problems.

 

[SwimmingNathan]

First of all, just to clarify, we're not taling about columns A, B and C. We're talking about columns M, N and O. You have a Worksheet_Change macro for the worksheet, that enters the date in column M when you populate column N. It seems to be working as it should. Column N changes only when you enter a value in clumn M. I don't see any problems.

Sorry I said A, B and C for the sake of simplicity. It works well for the most part, but after about 50 rows have been entered some values in Col N will change randomly. I was wondering if there was a way to change the Macro so that once a date is entered in Col N it won't change unless done so manually. Might just be a local error, who knows.

Thanks for your help anyway, much appreciated. Sorry for wasting your time with this part.

 

[mumps]

As far as I know, the macro either works for ech row or it doesn't. I don't know of any way around that.