Excel Budget Limit Warnings
November 6, 2018
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
If you build workbooks for other people to use, you might need to add controls, so they enter things correctly. In today's example, there is a budget sheet, with a limit on the budget total.
To ensure that people don't go over the limit, use a custom rule in data validation, with an error alert if they go over the limit. They can enter any amount for each budget item, as long as the total is below the limit. (You could move the Budget Limit cell to a different worksheet)
TIP: When you type the Error Message, press Enter to start a new line. That will make your message easier to read.
Go to the Data Validation Custom Rules on my website, to see more examples.
Here's another way to control what people enter on a worksheet -- show an error message with their name. That will catch their attention! Their name is at the top of the sheet too, in a formula cell.
To try it, click here to download my Personalized Message sample file (contains macros).
On the Budget sheet, type 2000 for the Travel amount, and a personalized message will appear. Click OK, and the Travel amount is cleared, so you can try again.
Here are a few details on how it works:
Go to my Contextures blog, to see another Personalized Message example. It shows a message when the file opens, and when you click a picture on the worksheet.
TODAY - Today (Nov 6th), the Excel team from Microsoft will have another Ask Me Anything (AMA) session on Reddit. The session starts at 2 PM Eastern time, and use this link to join in when it starts. Ask your important Excel questions, or drop in to see what other people ask! (Level - Int/Adv)
Dashboards - From now until November 15th, Mynda Treacy is hosting free webinars on how to build effective Excel Dashboards. Registration is also open for Mynda's highly-rated Dashboard course, and there is an early-bird discount of 20% off, if you register by Thursday (Nov 8th). Click here for the Excel Dashboard Course or the Power BI Course. (Level - Int/Adv)
Here are a few Excel articles that you might find useful or interesting.
It's Complicated - Don Tomoff shared 5 tips for understanding a complicated spreadsheet. This will help if you ever have to take over an Excel file that someone else built (or one that you built, long ago!) (Level - Int/Adv)
Excel Tips - See a collection of Excel tips on the Microsoft site. Click on a title to open that tip. Click the arrows to see more tips, and follow the links for details. (Level - All)
We went to the Apple store in Mississauga last week, to get new iPhones. The bigger screen is nice, but the Home button is gone, and it takes a bit of practice to learn the new gestures.
It was a grey, rainy day, but the bright leaves added a touch of colour. That's Mississauga's Civic Centre in the light brown brick -- its design was inspired by the farms that used to cover most of our city.
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2018/20181106ctx.html
I'll also post any article updates or corrections there.
That's it for this week! If you have any comments or questions, send me an email.
Last updated: November 6, 2018 9:08 AM