Alert Creation for milestone

[fwoody]

Hi,

I run a sports club (cricket) and keep a tally of all the stats for the team. I am trying to create alerts when certain cells reach certain levels for the first time.

Each year has a spate sheet with all the stats with a sheet totalling all the stats. So for example, over the last 4 years a player has scored 924 runs and I want to be alerted when his total reaches 1000, 2000, 3000 etc. Preferably by some sort of notification.

I thought I could do this by using conditional formatting but that only works if the player hits 1000 exactly.

Any help would be appreciated.

 

[gallen]

Could you use conditional formatting to have a certain colour if greater than 999 and less than 2000? A different colour for greater than 1999 and less than 3000 etc...?

Failing that you are in to Macro territory

 

[fwoody]

Could you use conditional formatting to have a certain colour if greater than 999 and less than 2000? A different colour for greater than 1999 and less than 3000 etc...?

Failing that you are in to Macro territory

Thanks Gallen. That definitely works.

I would love to be really clever and work out how to write a macro that would alert me to a milestone but slightly above me skill set at the moment!

 

[gallen]

it's fairly straight forward. Every time a cell changes, you check if it's a 'run total' cell and if so, check it's value and pop up a message box if it's a certain value. What is the layout of your sheet?

 

[fwoody]

There are different sheets for each year with stats for each player including runs, wickets etc. Then there is a 'total' sheet which uses vlookup to aggregate the data to get totals for all the stats.

 

[gallen]

If you want a macro writing for you, you need to be much more specific. Which cells exactly, when changed need to trigger the alert? I assume you only want the alert once so it doesn't trigger every time it increases? If you just want pointing in the right direction, then you need to look at the 'Worksheet_Change' event which is triggered everytime any cell value changes. You then need to check if it's a cell you are interested in and if so, check if the change needs to trigger an alert...

 

[fwoody]

Ok I have read a bit about macros and beginning to understand a little bit but still can't quite get it right....

To keep it simple let's assume the page is as follows:

A3 = 'name' with names below from a4 to a10

B3 = '2014' with number of runs pulled in from sheet 2014 using vlookup

B4 = '2015' etc

Up to

F3 = 'Total', where F4 adds a4 to e4

Let's say f4 is currently 924. After the next match the player scored 100, which is inputted into sheet '2017'. F4 in total will then become 1024 and would like it to alert me of this without having to open the sheet. The alert would hopefully say 'player name' has scored 1000 runs!

This would only happen the first time it is over 1000 and then the next time would be 2000 etc etc.

Does that make sense?

 

[gallen]

Yes, makes perfect sense. Thanks

The issue is that when using a vlookup, the change event doesn't seem to trigger. So I'm at a bit of a loss. I reckon I could make it work by using a 'ghost' sheet to remember old values and to log if a notification has been displayed but seems a lot of effort, certainly not something I personally have time to write. Even a simple ghost sheet that just logs if the user has been notified of each milestone then when you open the sheet, it checks the current values, sees if you SHOULD be notified then checks if you HAVE been notified, and if not, pop up a message box.

Sorry I can't be much more help. If no one else runs with this, my advice would be to break it down in to small tasks and get each one working (so set up a hidden a sheet that contains all names on the main sheet that has yes/no columns remembering if a notification has been sent and so on.)

 

[fwoody]

Thanks very much for all your help - will look into to that.

If anyone does want to write it... do go ahead(!!).

 

[Fluff]

@fwoody
Can you show an example of your 2017 sheet?