Formula to give the date another cells data changed?

andrew_milonavic

Board Regular
Joined
Nov 16, 2016
Messages
91
Hi,

If I have a formula in A1, is it possible to have a formula in B1 that would tell me the date A1 changed? So if A1 was Cat and the workbook/sheet recalculated and A1 stayed cat then the date in B1 wouldn't change. If A1 changed from cat to dog, or anything other than cat, then B1's date would update.

Possible?

Thanks

Andrew
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Not with a formula, but you could do that with VBA.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Set c = Intersect(Range("A1"), Target)
If c Is Nothing Then Exit Sub
Application.EnableEvents = False
    If UCase(c) <> "CAT" Then Range("B1") = Date
Application.EnableEvents = True
End Sub

Copy the code above.
Right click on the sheet tab where you want this to happen on.
Click View Code.
Paste into white area.
Hit Alt-q
Save the workbook as type .xlsm
 

andrew_milonavic

Board Regular
Joined
Nov 16, 2016
Messages
91
Hey Scott,

Thanks for the suggestion, was really hoping for a formula solution, but if it' not possible I'll test your VBA.

Thanks

Andrew
 

Watch MrExcel Video

Forum statistics

Threads
1,123,231
Messages
5,600,427
Members
414,384
Latest member
joehalks

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