Date-stamp based on cell range

[w0kkie]

Hello all! I am new as I'm sure you can tell, I've been getting more interested in excel capability this past year as time allows - I've found many features that have proven very useful at home and at work. However, I'm stumped on one thing that I've tried to figure out some months ago and left it alone after some failed attempts, but now I have another project I'm working on where this would be extremely helpful to have working, so I would love to see if anyone is willing to share their knowledge/ideas.

Here's the context:
I have a workbook containing about 10 worksheets (some may be removed or added periodically) at any given point in time. The second sheet remains in the workbook permanently, and is frequently edited - multiple times per week, sometimes daily or even multiple times per day, and by a handful of different users. This sheet is a project open issue list.

This sheet contains a bunch of info, organized as follows:
Header info: Rows 1-3
Issue list: Rows 4-500
Columns A - Q are used, nothing after Q. Applies to both the header rows and the issue list rows.

Columns contain fields such as issue number, priority, department, title, description, responsible party, due date, date assigned, date added, status notes/comments, and a couple other fields. Each issue is on it's own row.

The header rows have instructions and some reference data, and is not updated regularly. As you can imagine, this particular sheet is very important to keep track of, and to know when it was last updated.

Here's the problem:
Some of the people who edit this workbook often forget to include the date when making their updates, and sometimes other sheets in the workbook might get updated, but this particular sheet does not receive any update - which makes the NOW() and TODAY() functions utterly unhelpful.

Here's what I am trying to accomplish:
1. First, I want to have a "Last Updated on: " column on each row of the open issue list (meaning applicable to rows 4-500) that is auto-populated with the current date/time whenever any cell within Cols A-Q of that row is updated (inclusive of blank cells being populated for the first time, already populated cells being updated, or previously populated cells being cleared).
1. a) I did find some info online that got me close, but only refers to one specific cell (for example, if cell A2 changes, update date/time in cell B2) and I was not able to modify the formula to monitor/respond to updated within a range of cells.
-> For reference, that formula was:

=IF(A2<>"",IF(AND(B2<>"",CELL("address")=ADDRESS(ROW(A2),COLUMN(A2))),NOW(),IF(CELL("address")<>ADDRESS(ROW(A2),COLUMN(A2)),B2,NOW())),"")

2. Second, I want to have one cell which is on the first or second row (so, part of the header info that is not to be modified by users) that watches the entire open issue list table (which basically means A4:Q500) and is populated with the current date, each time any change is made to that range of cells. That way, there is always a quick-reference to see at a glance when the last change was made to the open issue list data.
2. a) I thought about just using a formula to copy the value of the latest date/time once I get the first point figured out, but I also want to be able to implement this one on other sheets as well, where there may not be any row-by-row date/time stamp cells.

In an ideal world, it would be great to find a solution that does not require VBA/macros, because this workbook gets shared external to my company at times, and there are some file extension filters that can block macro-enabled workbooks, as well as some people who have been instructed never to "enable macros" from an externally shared workbook.

I appreciate any help or insight, and look forward to seeing what ideas are out there for this! Please let me know if anything is unclear/confusing, and I'll try my best to clean it up.

 

[Yongle]

You cannot do what you want without VBA

Try this - which must be placed in sheet code window
(activate correct sheet \ right-click on sheet tab \ click on View Code \ paste code into the window that opens)

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Issues As Range, Cell As Range, stamp As Date
    Set Issues = Range("A4:Q500")
    If Not Intersect(Target, Issues) Is Nothing Then
        stamp = Now
        Application.EnableEvents = False
            Cells(1, "R") = stamp
        For Each Cell In Target
            Cells(Cell.Row, "R") = stamp
        Next Cell
        Application.EnableEvents = True
    End If
End Sub

 

[Yongle]

Consider
There are simple ways to prevent the sheet from being updated unless macros are enabled
- anyone not enabling VBA could be permitted to see but NOT update the sheet (or it could be hidden from them)
Do some of these other users actually require the workbook or simply the data ?
- when saved a macro could run automatically and create a data only version of the workbook

etc