Notification

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