Can Excel send Alerts? Yes, but with some limitations. Excel cannot email an alert to you automatically unless you write a macro in the Visual Basic (VBA) editor to perform this function. And, the reminder Alert only works if the Excel software is open. Not quite the convenient method you were hoping for, right?
Another option, although complicated and limited (at this time) to the XLS spreadsheet formats only, is to set up your spreadsheet like an Outlook Calendar, then import the data from one to the other. But this method is not really a satisfactory result either. So, until Microsoft decides to provide a functioning solution, we have to settle for work-arounds, using macros plus a little manual intervention for the email.
We've created two example spreadsheets for you to use while practicing these tasks:
The example spreadsheet in full, including macros:
Use this spreadsheet to practice creating Excel alerts and writing macros for them. Note: This spreadsheet includes the macros. JD Sartain
The example spreadsheet without the macros, in case you're unable to download the one with the macros.
Use this spreadsheet to practice creating Excel alerts and writing macros for them. Note: This spreadsheet does NOT include the macros. JD Sartain
Create the spreadsheet, and enter the formulas
You can setup your spreadsheet to alert you when a deadline is approaching or when the invoice is due using the Conditional Formatting feature. Then it can send an email to remind you that the invoice is due.
1. Download the Excel Alerts spreadsheet above (without macros) or create or use one of your own.
2. In cell A1, enter the function: =TODAY().
3. If you're building a spreadsheet from ground zero, enter the following field names in columns A, B, D, and E: Invoices/Debts, Amount Due, Due Day of the Month, Alert Cardinal #, and Alert Ordinal # on row 4. For column C, type Due Day, press Alt+ Enter (to add a second line), then type of the Month. Do the same for the stacked headers in the Alert columns E and F.
4. Highlight both columns C and D, then select Home > Merge & Center > Merge & Center (from the Alignment group). Center the remaining field names in columns A, B, E, and F.
5. Populate the database/spreadsheet with some data that matches the fields/column headers.
Because we do not want to create a separate spreadsheet for every month of the year, we can use Excel functions to match the days of the month to the =TODAY() function, which enters the current date in cell A1 for every single day, 365 days a year. But, unfortunately, day 10 (or the 10 th day of the month) does not match the current date; for example; February 27, 2019 does not = 27 or 27th. So, we'll use functions to make them compatible.
6. Enter the function =DAY(1) in cell C5; =DAY(2) in C6; =DAY(3) in C7; and so forth down to C34 (for 30 days). (You may enter 31 days if you like, but most bills are not due on the 31st because that day is not available in every month.)
7. Next, in cell A2 enter the function =DAY(A1).
8. If you prefer ordinal numbers (1st, 2nd, 3 rd, etc.), you can enter the cardinal numbers (1, 2, 3, 4, 5) in C5:C34, then add this formula in cell D5: =DAY(C5)&IF(OR(DAY(C5)={1,2,3,21,22,23,31}),CHOOSE(1*RIGHT(DAY(C5),1),"st","nd ","rd "),"th").
9. Copy the formula from D5 down through D34 (D5:D34).
10. Add this same formula to cell B2 (just copy it from D5 to B2, and Excel adjusts the formula to compensate for the new location).
The DAY() function converts the =TODAY() date to a number (e.g., 1, 2, 3), which corresponds with one of the 30 days in any month. So, regardless of what month the TODAY() function displays, A2 displays only the day.
11. Now we need the Alert formulas for columns E and F. Enter this formula in cell E5: =IF(C5=$A$2,"DUE NOW", 0). Use Function key F4 to add the $ (dollar) sign, which makes A2 an absolute cell reference (that is, when we copy this formula, column C changes as we copy down, but cell A2 remains the same).
12. Copy the formula in E5 down from E6 through E34.
13. If you prefer the Ordinal numbers, copy this formula into cell F5: =IF(D5=$B$2,"DUE NOW", 0), then copy the formula in F5 down from F6 through F34.
JD Sartain / IDG WorldwideCreate and populate the spreadsheet, then enter formulas.
Now we can create a Conditional Formatting Rule to identify the bills that are due now.
Use Conditional Formatting to create Excel Alerts
1. Highlight E5:E34, then select HOME > Conditional Formatting > New Rule.
2. In the New Formatting Rule dialog box under Select a Rule Type, choose the second option on the list: Format Only Cells that Contain.
3. In the Edit the Rule Description box under Format Only Cells With, choose Specific Text from the first field's drop-down list, Containing from the second field's drop-down list, and then enter the words DUE NOW in all caps in the third field box.
4. Next, click the Format button beside the Preview box.
5. The Format Cells dialog opens. In the Underline field box, choose None form the drop-down list.
6. Under Effects, ensure that none of the boxes are checked or blacked-out.
7. Click the arrow beside the Automatic field box and choose a color from the palette (e.g., Red). Notice that the field box name changes from Automatic to Color.
8. In the Font Style box above the Automatic/Color box, select Bold, then click OK and you did it!
Look down column E Alert Cardinal # for today's date (in this case, February 27th, row 31): The words DUE NOW appear in cell E31, in bold red. Tomorrow, the DUE NOW words will appear on row 32, which corresponds with tomorrow's date of February 28th.
NOTE: If you prefer to work with the Ordinal numbers, follow the instructions above, exactly, except use the Alerts Ordinal # columns, that is column F, which means the range will be F5:F34.
JD Sartain / IDG WorldwideUse Conditional Formatting to create your Alerts
Hmmm, that works great if you don't mind waiting until the very last due date to pay your bills. Let's create a formula that gives us a few days' notice.
This addition to your spreadsheet is incredibly simple. In cell A1, you have the formula =TODAY() and Excel returns today's date. Press Function key F2 to edit cell A1 and add +4 to the end of your formula; that is: =TODAY()+4.
Notice that Excel KNOWS that Feb 27th plus 4 equals March 3 rd, even though we're only working with day numbers and not days of the month. Otherwise 27 + 4 would equal 31. Pretty smart program, huh? So, now you have four days' notice before your DUE NOW bills are actually due.
JD Sartain / IDG WorldwideModify one formula for a four-day notice on bills due.
Prep & email the spreadsheet
As I mentioned previously, this is not automatic. You could write a macro to do this, but you would still have to open Excel to run the macro.
If you want to use a macro, which is a bit faster than manually performing these steps, follow the instructions below.
NOTE: Before you begin the macro, decide whether you want to use column E Cardinal Numbers or column F Ordinal Numbers. Delete the column you decide not to use. Notice that the keystrokes are printed in Bold and the comments are in (parentheses).
Turn the Macro Recorder on
1. Click the Developer tab, then click the Record Macro button
2. Under Macro Name, type Alerts.
3. Under Shortcut Key, type Shift- M. Excel adds the Ctrl key, so the actual macro shortcut keys are Ctrl+ Shift- M.
NOTE: This is a simultaneous keystroke, which means you press the Ctrl key and hold, press the Shift key and hold, then press the letter M, and release all three keys simultaneously.
4. Under Store Macro In, choose This Workbook (from the list).
5. Under Description enter this text: Located the DUE NOW bill and moves the creditor and amount owed to the top of the spreadsheet.
6. Click OK.
Prep the spreadsheet
Begin recording the following keystrokes (carefully):
1. Press Ctrl+ Home
2. Right, Right, Right, Right (to delete column E)
or…
3. Right, Right, Right, Right, Right (to delete column F)
NOTE: Do not delete both the E and F columns, just one or the other.
4. Click the Home tab.
5. Click Delete (in the Cells group).
6. Click Delete Sheet Column (from the drop-down list).
7. Ctrl+ Home
8. Ctrl+ F (The cursor automatically defaults to the Find What field box. Type the search word in this box.)
9. Enter: DUE NOW (in all caps).
10. Click the Options button.
11. Click the Look In field box and choose Values from the list.
12. Click the Find Next button.
13. Click the Close button.
14. Left, Left, Left, Left
15. Hold down the Shift key and press Right (one time).
16. Ctrl+ C
17. Ctrl+ Home, Right (one time).
18. Ctrl+ V
19. Click the Home tab, and choose the Font group.
20. Click Font Color Red, then click Bold.
21. Ctrl+ Home
Now A1 displays today's date (plus 4), B1 displays the creditor, and C1 displays the amount you owe. It doesn't seem like much of a benefit if you only have 30 or so records/rows showing on a single screen, but if you have 500 records/rows, it's nice to have the bill that's due pop up at the top of the spreadsheet on row 1.
NOTE: If you do not have the Email button on your Ribbon menu, click File > Options > Customize Ribbon, and add the Email button to the View tab. Read my story on customizing the Word Ribbon menu for more information (it works the same in Excel as it does in Word). Do not stop in the middle of the macro to do the above procedure. Add the button first, then go back and record the macro.
Email the DUE NOW spreadsheet with a message
The following instructions are part of the above macro; however, the macro pauses while you're in Outlook.
22. Click the View tab.
23. Click the Email button.
Excel opens Microsoft Outlook with a New Email displayed on the screen.
24. The cursor is in the To field; enter the recipient's email address here.
25. Enter additional email addresses in the CC: field for anyone who should receive a copy of this email.
Notice that the Alerts spreadsheet is already attached, and the name of the attachment spreadsheet, Alerts.xlsx, is in the subject line.
26. Position the cursor in the body of the email and enter the following:
Electric Bill: $2500.00 due March 3
NOTE: Don't bother Copying cells A1:C1 so you can paste them in Outlook. It won't work. Once you enter Outlook, the macro pauses. Everything you do in Outlook must be re-keyed every time. And, you cannot return to Excel until after the open email is sent or cancelled.
27. Click the Send Email button.
Outlook closes and returns you and your cursor to Excel.
28. Click the Developer tab > Stop Macro button.
JD Sartain / IDG WorldwideRecord the macro and email the spreadsheet.
Test your macro
1. Press Ctrl+ Shift- M. (Press the Ctrl key and hold, press the Shift key and hold, press the letter M, and then release all three keys simultaneously.)
2. The macro runs in a second, then opens Outlook for you to enter the email information.
3. And, once again, after you click Send, you're returned to the open Excel spreadsheet.
Save the spreadsheet as a macro spreadsheet, such as Alerts.xlsm, and exit.
Không có nhận xét nào:
Đăng nhận xét