Hi all,

I am stumped as to how you would get some form of notification when
something is added to a 'master' spreadsheet.

If many people were using this spreadsheet and adding a row of
information (an entry), each entry having a unique identifiable number
in column A, how would you get something out once a day which said
"number, number, and number have been added" taking the numbers from
column A for each new entry?

Is this possible? i am thinking perhaps an email, or populating a
report, or any form really perhaps even printed but reliable so ones
that were added were not missed.

So for instance,
Darren would use the sheet from 9 til 12 adding entries,
Duncan would use the sheet from 12 til 3 adding entries.
at 4 o clock an email/printout/report would come from somewhere and get

given to Jon who would then know what new entries had been added.

Any ideas?

Duncan,
Is it possible to date/time stamp entries in a "spare"
column and are all entries new or can there be updates to existing entries?

You could use the "Worksheet_Change" event to monitor changes and record
new additions by matching the unique ID against existing ones; if not matched
then it must be new.



"Duncan" wrote:

Hi all,

I am stumped as to how you would get some form of notification when
something is added to a 'master' spreadsheet.

If many people were using this spreadsheet and adding a row of
information (an entry), each entry having a unique identifiable number
in column A, how would you get something out once a day which said
"number, number, and number have been added" taking the numbers from
column A for each new entry?

Is this possible? i am thinking perhaps an email, or populating a
report, or any form really perhaps even printed but reliable so ones
that were added were not missed.

So for instance,
Darren would use the sheet from 9 til 12 adding entries,
Duncan would use the sheet from 12 til 3 adding entries.
at 4 o clock an email/printout/report would come from somewhere and get

given to Jon who would then know what new entries had been added.

Any ideas?

Toppers,

For this gain all additions will be new, but if i can get something
working then each 'record' will be have three actions (this first one
for it being added being one of those three)

I have free rein on the spreadsheet so i can add collumns if i need so
that is not a problem, the hardest part for me is working out how to
structure the code that compares the entries as you have suggested
against ones already on. I just think there must be something within
excel that would near fit my purpose! its so frustrating knowing what
you want but not how to get it.

I have been playing with the track changes feature in excel today and
looking at the 'history' which it populates to a new sheet, I am trying
to work out if i can get a macro to automate the production of this
'history' sheet which shows all changes made and for the macro to
analyse the sheet and come back with the new additions. just a lot to
work out really and hoping for a miracle!

Duncan,
Try this as a starter:

HTH

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo exitsub
Application.EnableEvents = False

If Target.Count 1 Then Exit Sub ' more then one cell selected ..

Set ws2 = Worksheets("sheet2") ' record of updates is on this sheet

Set isect = Application.Intersect(Target, Range("A:A"))

If Not isect Is Nothing Then
' look for match in column A ....
res = Application.Match(Target.Value, Range("A:A"), 0)
If Not IsError(res) Then ' no match so must be new value
' update tracking details ...e.g. worksheet to record to key &
date time
MsgBox Target.Value & " has been added to file on " & Now()
nextrow = ws2.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws2.Range("a" & nextrow) = Target.Value
ws2.Range("b" & nextrow).End(xlUp)(2) = Now()
End If
End If
exitsub:
Application.EnableEvents = True
End Sub
"Duncan" wrote:

Toppers,

For this gain all additions will be new, but if i can get something
working then each 'record' will be have three actions (this first one
for it being added being one of those three)

I have free rein on the spreadsheet so i can add collumns if i need so
that is not a problem, the hardest part for me is working out how to
structure the code that compares the entries as you have suggested
against ones already on. I just think there must be something within
excel that would near fit my purpose! its so frustrating knowing what
you want but not how to get it.

I have been playing with the track changes feature in excel today and
looking at the 'history' which it populates to a new sheet, I am trying
to work out if i can get a macro to automate the production of this
'history' sheet which shows all changes made and for the macro to
analyse the sheet and come back with the new additions. just a lot to
work out really and hoping for a miracle!

Toppers, You are smashing.

This worked with no fiddling (except to move 'date time' onto its
proper remmed line but thats a fault of the google message box!)
I will experiment further with this and see how far i can go with it.

Many sincere thanks,

Duncan