Date stamp when cell changes for a number of rows.

Hi All,

I have a problem that has been nearly answered on a number of threads, just with a slight subtle difference.

I want to add a date stamp above a cell when the value in that cell changes, this will be for a number of alternate rows. For example.

Row 1: Date 1 Date 2 Date 3
Row 2: Change 1 Change 2 Change 3
Row 3: Date x Date y Date z
Row 4: Change x Change y Change z

(dates appear in rows 1 and 3 when a change is made in 2 /4)

This is to be done for around 600 rows. I have found this VBA code that seems to be along the rite lines but dont know how to apply it to a large number of alternating rows.

Please Login or Register  to view this content.

Any help would be great!

Hi, the code you found is almost ready :-)
try :

Please Login or Register  to view this content.

PS. note how readability of the code improved after applying code tags and follow this and othe requirements of our http://www.excelforum.com/forum-rule...rum-rules.html (please edit your post above and use code tags there too).

Not that easy, because calculate event handler has no Target argument - we dont know what has been changed.
of course one can make an archieve copy of the range we focus on, and then after calculation compare currrent state of this copy and present values in cells. or may be your input data (input for formula) is reasonably structured - then you can monitor this data with SheetChange eveny and write change date in appropriate cell.

Anyway probably standard exempt from forum rules (again I kindly ask you to edit first post and use code tags) would be appropriate:

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

I probably dont follow this. To help I shall understand, what triggers some action occuring in the workbook. Where is source of data to be processed in this action, and where the output shall be inserted.

Next attempt based on (As I understood) sample workbook:

Please Login or Register  to view this content.

This code is in MAster ... worksheet code . Rightclick on this worksheet tab and select "edit code" to see it in attached file.
Note my local date foremat I used (2015-06-12) and adjust it to your local one in this row:

Please Login or Register  to view this content.

"mm/dd/yyyy" or any other which is suitable

change something in even row of column B in Master sheet. Add something after ## to existing data etc. Shortly: play and check different options to test how it works.

Originally Posted by kcaseykc

If theres a way to 'lock' the dates in so they wont change when a new note is added onto the end then we really would have this problem solved once and for all!

well, it works that way. I downloaded a file from my previous post, edited B8 in Master sheet (was reading anna) to anna##maria and presto: in sheet2 in B7:C8 I got:

HTML Code: 

So I probably do not understand your point. One thing to note - nothing changed in previous content of the cell - just added double hash and new part of text

1) you tried to increase the distance between lines in Notes View, but you missed *2 while checking:

Please Login or Register  to view this content.

2) note that adding new note not after just "##" but after " ## " leads to the above test being true also for last "in users view unchanged" note (the one before new added one), so for example if in your row 6 if you have
|Note 1 ## Note 2|
and add Note 4 this way:
|Note 1 ## Note 2 ## Note 4|
then in your Notes View
| Note 2|
changes into
| Note 2 |
(extra space after)

Proposed solution (geting rid of leading space too):

Please Login or Register  to view this content.

in UK yyyy-mm-dd seems to follow BS ISO 8601:2004 so may be this is the reason.