Add Alarms to Phone via Excel Upload
Can Excel transport Alerts? Yep, but with some limitations. Excel cannot email an warning to you lot automatically unless y'all 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 up. Non quite the convenient method y'all were hoping for, right?
Another option, although complicated and limited (at this fourth dimension) to the XLS spreadsheet formats only, is to set up your spreadsheet like an Outlook Agenda, and then import the data from one to the other. Only this method is not really a satisfactory result either. And then, until Microsoft decides to provide a functioning solution, we have to settle for work-arounds, using macros plus a picayune manual intervention for the e-mail.
Nosotros've created ii example spreadsheets for you to use while practicing these tasks:
The instance spreadsheet in total, including macros:
Use this spreadsheet to do creating Excel alerts and writing macros for them. Notation: 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 tin can setup your spreadsheet to alarm you when a deadline is approaching or when the invoice is due using the Conditional Formatting feature. Then it can transport an email to remind you that the invoice is due.
one. Download the Excel Alerts spreadsheet above (without macros) or create or use one of your own.
2. In cell A1, enter the office: =TODAY().
3. If you're edifice a spreadsheet from basis zip, enter the following field names in columns A, B, D, and E: Invoices/Debts, Amount Due, Due Twenty-four hours of the Month, Warning Cardinal #, and Alert Ordinal # on row iv. For column C, blazon Due Day, press Alt+ Enter (to add together a 2d line), then type of the Month. Practise the same for the stacked headers in the Warning columns E and F.
iv. Highlight both columns C and D, so selectDwelling > Merge & Eye > Merge & Centre (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/cavalcade headers.
Because we do not want to create a separate spreadsheet for every month of the year, we tin use Excel functions to match the days of the month to the =TODAY() function, which enters the current engagement in cell A1 for every single day, 365 days a year. Simply, unfortunately, day x (or the 10 th mean solar day of the month) does not friction match the electric current date; for example; February 27, 2019 does non = 27 or 27th. Then, nosotros'll use functions to make them compatible.
6. Enter the office =Twenty-four hour period(ane) in cell C5; =24-hour interval(2) in C6; =Mean solar day(3) in C7; and so forth downwardly to C34 (for 30 days). (You lot may enter 31 days if you like, merely most bills are non due on the 31st because that day is non bachelor in every month.)
7. Next, in cell A2 enter the part =24-hour interval(A1).
8. If you prefer ordinal numbers (1st, 2nd, 3 rd, etc.), you can enter the primal numbers (1, 2, 3, 4, five) in C5:C34, then add this formula in cell D5: =DAY(C5)&IF(OR(Mean solar day(C5)={1,ii,iii,21,22,23,31}),CHOOSE(1*RIGHT(Solar day(C5),1),"st","nd ","rd "),"thursday").
9. Copy the formula from D5 down through D34 (D5:D34).
ten. Add this aforementioned formula to cell B2 (just copy it from D5 to B2, and Excel adjusts the formula to compensate for the new location).
The 24-hour interval() function converts the =TODAY() date to a number (e.g., ane, 2, 3), which corresponds with i of the 30 days in whatever month. So, regardless of what calendar month the TODAY() function displays, A2 displays only the 24-hour interval.
11. Now nosotros 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 accented prison cell reference (that is, when we re-create this formula, cavalcade C changes as we re-create down, merely cell A2 remains the same).
12. Copy the formula in E5 down from E6 through E34.
13. If you prefer the Ordinal numbers, re-create this formula into cell F5: =IF(D5=$B$2,"DUE NOW", 0), then re-create the formula in F5 downward from F6 through F34.
JD Sartain / IDG Worldwide Create and populate the spreadsheet, and then enter formulas.
Now we can create a Provisional Formatting Rule to identify the bills that are due now.
Use Conditional Formatting to create Excel Alerts
1. Highlight E5:E34, and so select Dwelling > Conditional Formatting > New Rule.
2. In the New Formatting Dominion dialog box underSelect a Rule Blazon, choose the 2nd option on the listing:Format Only Cells that Contain.
3. In the Edit the Dominion Clarification box under Format Only Cells With, choose Specific Text from the first field'due south driblet-down listing, Containing from the second field's drop-down list, and then enter the words DUE At present in all caps in the third field box.
4. Side by side, click the Format button beside the Preview box.
v. The Format Cells dialog opens. In the Underline field box, choose None form the driblet-down listing.
6. Under Furnishings, ensure that none of the boxes are checked or blacked-out.
seven. Click the pointer 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 Way box to a higher place the Automated/Color box, select Assuming, then click OK and you did it!
Wait down column E Alert Fundamental # for today's date (in this case, February 27thursday, row 31): The words DUE NOW appear in jail cell E31, in bold scarlet. Tomorrow, the DUE NOW words will appear on row 32, which corresponds with tomorrow's appointment of February 28th.
Note: If you prefer to work with the Ordinal numbers, follow the instructions above, exactly, except employ the Alerts Ordinal # columns, that is cavalcade F, which means the range will be F5:F34.
JD Sartain / IDG Worldwide Use Provisional Formatting to create your Alerts
Hmmm, that works groovy if you lot don't heed waiting until the very last due engagement to pay your bills. Let's create a formula that gives us a few days' discover.
This improver to your spreadsheet is incredibly simple. In cell A1, you lot accept the formula =TODAY() and Excel returns today's date. Press Office key F2 to edit cell A1 and add +four to the cease of your formula; that is: =TODAY()+4.
Detect that Excel KNOWS that Feb 27thursday plus four equals March iii rd, even though we're just working with mean solar day numbers and non days of the calendar month. Otherwise 27 + 4 would equal 31. Pretty smart program, huh? So, now you take iv days' notice earlier your DUE NOW bills are actually due.
JD Sartain / IDG Worldwide Modify one formula for a four-twenty-four hours detect on bills due.
Prep & email the spreadsheet
Equally I mentioned previously, this is not automatic. You could write a macro to do this, just you would still have to open Excel to run the macro.
If you lot desire to utilise a macro, which is a bit faster than manually performing these steps, follow the instructions below.
Notation: Before you begin the macro, decide whether you desire to use column E Key Numbers or column F Ordinal Numbers. Delete the column you decide non to use. Notice that the keystrokes are printed in Bold and the comments are in (parentheses).
Plow the Macro Recorder on
one. Click the Developer tab, and then click the Record Macro button
2. Nether Macro Proper noun, blazon Alerts.
3. Under Shortcut Central, type Shift- 1000. Excel adds the Ctrl key, so the actual macro shortcut keys are Ctrl+ Shift- M.
Annotation: This is a simultaneous keystroke, which means you press the Ctrl primal and agree, printing the Shift key and hold, then press the alphabetic character K, and release all iii keys simultaneously.
4. Nether Store Macro In, choose This Workbook (from the list).
five. Under Description enter this text: Located the DUE At present bill and moves the creditor and amount owed to the top of the spreadsheet.
vi. Click OK.
Prep the spreadsheet
Begin recording the following keystrokes (carefully):
1. Press Ctrl+ Home
2. Right, Right, Correct, Correct (to delete column E)
or…
3. Right, Correct, Right, Right, Right (to delete column F)
NOTE: Do not delete both the East and F columns, merely one or the other.
iv. Click the Home tab.
5. Click Delete (in the Cells group).
6. Click Delete Sheet Column (from the drib-downwards list).
vii. Ctrl+ Home
viii. 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.
eleven. Click the Look In field box and choose Values from the list.
12. Click the Find Next button.
13. Click the Close push.
14. Left, Left, Left, Left
15. Agree downward the Shift key and press Right (one time).
xvi. Ctrl+ C
17. Ctrl+ Domicile, Right (one fourth dimension).
eighteen. Ctrl+ V
19. Click the Home tab, and choose the Font group.
20. Click Font Color Scarlet, then click Assuming.
21. Ctrl+ Home
Now A1 displays today's appointment (plus 4), B1 displays the creditor, and C1 displays the amount you owe. It doesn't seem similar much of a do good if you only have 30 or and so records/rows showing on a single screen, simply if you lot have 500 records/rows, it's prissy to take the bill that's due pop upwardly at the top of the spreadsheet on row 1.
Note: If you lot exercise not have the Electronic mail 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 Give-and-take Ribbon menu for more data (it works the same in Excel as it does in Discussion).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.
E-mail the DUE At present spreadsheet with a bulletin
The following instructions are role of the above macro; still, 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 Electronic mail 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 fastened, and the name of the attachment spreadsheet, Alerts.xlsx, is in the subject line.
26. Position the cursor in the trunk of the email and enter the following:
Electric Nib: $2500.00 due March 3
NOTE: Don't bother Copying cells A1:C1 and then you can paste them in Outlook. Information technology won't work. In one case yous enter Outlook, the macro pauses. Everything you practise in Outlook must be re-keyed every time. And, you cannot return to Excel until after the open electronic mail 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 > End Macro button.
JD Sartain / IDG Worldwide Record the macro and email the spreadsheet.
Exam your macro
1. Press Ctrl+ Shift- M. (Press the Ctrl key and hold, printing the Shift key and hold, press the alphabetic character Thou, and so release all three keys simultaneously.)
2. The macro runs in a second, so opens Outlook for you to enter the electronic mail information.
3. And, once again, after you click Transport, you're returned to the open Excel spreadsheet.
Save the spreadsheet as a macro spreadsheet, such every bitAlerts.xlsm, and go out.
Source: https://www.pcworld.com/article/403377/create-excel-alerts-then-write-a-macro-to-email-them.html
0 Response to "Add Alarms to Phone via Excel Upload"
Post a Comment