Contextures

Contextures News 20181106

Excel Budget Limit Warnings

November 6, 2018

Budget limits, personalized messages, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Budget Limit

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)

  • To set up the rule, select cells B2:B7, and on the Data tab, click Data Validation
  • From the Allow drop down, choose Custom, and enter this formula:
    • =SUM($B$2:$B$7)<=$B$10
  • (optional) On the Error Alert tab, enter a Title and Error Message
  • Click OK, and then type 2000 as the Travel amount, to see the warning.

TIP: When you type the Error Message, press Enter to start a new line. That will make your message easier to read.

data validation warning

Go to the Data Validation Custom Rules on my website, to see more examples.

Make It Personal

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.

personalized message

Here are a few details on how it works:

  • Set the budget limit on the Admin sheet
  • Choose which type of User Name to show - Network or Excel application.
  • Formula in cell C6 has User Defined Functions (UDF) to show user name, based on which type you selected. See the UDFs on the modFunctions module, in the Visual Basic Editor.
    • =IF(UserNameType="Application", Get_User_Name_App(), Get_User_Name())
  • To see the code that runs when you change a budget amount, right-click the Budget sheet tab, and click View Code.

Admin sheet setup

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.

Reminders

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)

Excel Articles

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)

Also see: My Excel Products || Excel Events || Previous Issues || Weekly Humour

Rainy Day

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.

weekly photo

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.

Debra Dalgleish
dsdalg@ gmail.com

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

 

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: November 6, 2018 9:08 AM