If an alert condition exists then an appropriate email is sent containing the details of the alert(s) (what kind of alert, who is involved, relevant dates, etc).
Excel 2016 review tracking windows#
Using the Windows "Task Scheduler" to open a spreadsheet daily (at 1am), the Workbook_Open macro then runs checking for alerts (this can be multiple alerts, as many as you like). My new solution when I get round to coding it will be a bit more generic.ĪND of course if the computer this resides on is powered off then nothing is going to happen.Īs you'll gather it's a non-trivial task, another contributor to this forum suggests an app called AlladinPRO () but I've not looked at this myself but it is a paid for you ask for can be done in Excel VBA but it is a non-trivial task and would have to be closely tailored to your spreadsheet, and possibly what email Client you use.
Excel 2016 review tracking code#
Some elements like the actual sending of emails is generic, but much of the rest of the current code is customised to the applications themselves. Even this is not perfect as the macro won't run if the particular spreadsheet is already open at the time (I've a solution to this but as yet not implemented). I use command line switches to indicate to the macro whether it is just me opening the spreadsheet to work on it or whether it is a scheduled running of the sheet. After running the spreadsheet it then closes itself automatically. The Task Scheduler is configured to determine the frequency. I use the Windows task scheduler to automatically run them at night some on a daily basis others alternate days, and another fortnightly. I have several spreadsheets which use a dedicated Gmail account to sent out emails automatically. You have to consider what happens if no one opens the spreadsheet/runs the macro for a few days? And then what action stops them being sent. every time the spreadsheet is opened? once only?, weekly?, daily?, hourly?. You then have to decide how often you want emails sent e.g. However, this is only part of the solution. To send emails you will have to resort to macros/VBA, this not difficult but the actual macro depends on the email client you have/want to use. If the due date is today or within the next seven days, then it shows up in Schick, If the due date is already past, then it shows up as red.
This is a two-tiered format, and you end up with two levels of alert. Click OK to close the Conditional Formatting dialog box.The finished Conditional Formatting dialog box. Click OK to close the Format Cells dialog box.Using the Color drop-down list, choose the color blue.Excel displays the Format Cells dialog box. In the formula area, enter "=TODAY()+7" (without the quote marks).Make sure the first drop-down list for the second condition is "Cell Value Is." (This should be the default.).The Conditional Format dialog box expands to include a second condition. Using the Color drop-down list, choose the color red.In the formula area, enter "=TODAY()" (without the quote marks).Make sure the second drop-down list is "Less Than.".Make sure the first drop-down list is "Cell Value Is." (This should be the default.) (See Figure 1.)įigure 1.Excel displays the Conditional Formatting dialog box. Choose Conditional Formatting from the Format menu.Select the cells that contain the document due dates.The effect of this formula is to alert you to any date that is either past or within the next week.Īnother approach is to use the conditional formatting capabilities of Excel. If so, then the formula displays "<<<" in the cell. The formula checks to see if the date in cell F3 is earlier than a week from today. Assuming your due date is in column F, you could place the following type of formula in column G: The first method is to simply add a column to your worksheet that will be used for the alert. There are several ways that this can be done in Excel, and you should pick the method that is best for your purposes. He wondered if there was a way for Excel to somehow alert him if the due date for a particular document was approaching. Jonathan developed a worksheet that tracks due dates for various departmental documents.