Control Budget With Excel Data Validation

If you’re building a budget in Excel, you can limit the total amount that is entered, to help prevent anyone from going over budget.

I shared this tip a few years ago, in this blog post: Limit the Total Amount Entered in Excel

data validation custom error message - over limit
data validation custom error message – over limit

New Features

In the new version, I’ve added a few more features, to help you fill in the correct amounts.

  • Below the Budget Limit, in cell D3, you can see the amount that hasn’t been added to the budget yet.
  • In column D, you can see the maximum amount that can be entered in each row, based on the entries in other rows. This makes it easier to adjust individual items, while you finalize the budget.
data validation in cell D2 limits budget amount
data validation in cell D2 limits budget amount

And remember, data validation isn’t foolproof, so you’ll still have to check those budgets, to make sure nobody is trying to get a little extra!

Download the Sample File

To see the formulas, and test the data validation, you can download the sample budget from my Contextures website.

Go to the Sample Excel Files page, and in the Data Validation Section, look for DV0058 – Limit Budget Entries with Data Validation.

Watch the Budget Limits Video

To see the steps for setting up the data validation and formulas, to set the budget limits, you can watch this short video tutorial.

_______________________________________

Line Between Dates in Filtered List

A while ago, I showed you how to add a red border at the top of a row, when a new date starts in a list.

You can read those instructions here: Separate Excel List with Automatic Lines.

Line Between Dates in Filtered List
Line Between Dates in Filtered List

Conditional Formatting Problem

And if you’re using this technique, remember the painful lesson that I learned – don’t copy and paste within a table that has conditional formatting.

You could end up with an Excel 2010 Conditional Formatting Nightmare. And nobody wants that!

conditional formatting duplicate rules problem
conditional formatting duplicate rules problem

Lines Disappear in Filtered Lists

Recently, I heard from Lon, who liked the tip about red line borders. But Lon noticed a problem – the line didn’t always show if the list was filtered.

For example, if we filter the Product column, to hide Paper, the red borders for some of the dates disappear.

Lines Disappear in Filtered Lists
Lines Disappear in Filtered Lists

I hadn’t noticed the problem, because my list is usually filtered by date, to show only the latest month’s data. Since the conditional formatting is based on the date column, it would continue to work correctly.

But we can change the conditional formatting, so it works in a filtered list.

Change the Formula for Filtered Lists

To make the conditional formatting work in a filtered list, we can’t use the original formula, which was

=$A1<>$A2

That formula just compares each date to the date above it, and doesn’t care if the rows are hidden or visible.

Instead, we’ll use a formula that was created by Laurent Longre. It lets you work with visible rows after a filter. For information on this formulas, read the Power Formula Technique section, in this article at John Walkenbach’s web site: Excel Experts E-letter

Here is the much longer formula that we can use, to compare dates in the visible rows only.

=SUMPRODUCT(SUBTOTAL(103,OFFSET($A$1:$A2,ROW($A$1:$A2)
-MIN(ROW($A$1:$A2)),,1)), –($A$1:$A2=$A2))=1

Note that there are two minus signs in front of the last open bracket – it’s not a long dash.

ConditionalFormatFiltered03

Date Separator Lines Show When Filtered

With the new formula, the red lines separate the dates, even if the list is filtered. In the screen shot below, the Product column is filtered to hide Paper, but the date line for July 18th shows up.

Date Separator Lines Show When Filtered
Date Separator Lines Show When Filtered

Download the Sample File

There is another example of this type of formula on my Contextures website – Count Visible Items in a Filtered List.

And you can download the sample file used in this blog from the Contextures Sample Excel Files page. In the Conditional Formatting section, look for CF0004 – Conditional Formatting in Filtered List. The zipped file is in Excel 2007/2010 format, and contains no macros.

_________________

Excel Books for Beginner to Intermediate

excel2010_missingmanual Stephan emailed me recently, and asked what books I’d recommend for an advanced beginner and for an intermediate user.

On the Contextures website, I’ve got a list of Excel books. They range from books for absolute beginners, to specialized books on statistics and financial modelling.

Book Suggestions

Here are the advanced beginner books that I suggested to Stephan – do you agree with these choices?

You might find these at your local library, or a nearby bookstore

  • Excel 2010: The Missing Manual; Matthew
  • MacDonald; ISBN: 1449382355; 898 pages; 2010; US$39.99
  • Microsoft Excel 2010 Step by Step; Curtis Frye; ISBN: 0735626944; 480 pages; 2010; US$29.99
  • Slaying Excel Dragons: A Beginners Guide to Conquering Excel’s Frustrations and Making Excel Fun; Mike Girvin, Bill Jelen; Holy Macro! Books; ISBN: 978-1615470006; 532 pages; 2011; US$29.95
  • Excel 2010 All-in-One For Dummies; Greg Harvey; ISBN: 0470489596; 792 pages; 2010; US$

Check the Contents

If you’re book shopping online, before you buy an Excel book on Amazon, use the “Look Inside” feature, if available, to see what the writing style is like, and check the table of contents.

The quick peek will give you a general overview of the book, and could help you decide if the book is right for you.

image

Read the Reviews

The customer reviews can be helpful too – both the negative and positive ones. Sometimes another person doesn’t like a book because it’s not for absolute beginners, and that might be a positive thing for you

. And there’s always the possibility that some of the glowing reviews were written by the author’s mother or friends! 😉

image

If you can get to a bookstore, you can flip through the Excel books there, and head home with a few that you like.

It sometimes costs a bit more than shopping online, but it’s worth it, to find the books that are best suited to your needs and learning style.

And it’s good to support your local businesses!

__________

Check Multiple Criteria with Excel INDEX and MATCH

Use INDEX and MATCH together, for a powerful lookup formula. It’s similar to a VLOOKUP formula, but more flexible — the item that you’re looking for doesn’t have to be in the first column at the left. Watch the video to see how it works (there are written instructions too), and download the sample workbook to follow along.
Continue reading “Check Multiple Criteria with Excel INDEX and MATCH”

Check Excel Database Before Adding New Item

When you build an Excel tool or template, it’s rare that you’re ever really finished building. There’s always something that would make the tool a little better, either for your own use, or for your customers.

And that’s the case with the Excel worksheet data entry form, which I’ve just updated again.

The original version was by Dave Peterson, and the form has evolved into a version in which you can add and update items in the database.

New Features

In the latest version, I fixed an issue with the navigation. Thanks to Travis, who let me know about the problem.

Now, when you move to a different record with the arrow buttons, the Order ID selector also updates.

You can see the Order ID, the Order ID selector, and the record number, circled in the screen shot below.

Order ID, Order ID selector, record number
Order ID, Order ID selector, record number

Add or Update

The other enhancement is a database check, when you click the Add or Update button. In a hidden column, a COUNTIF formula counts the selected Order ID occurrences in the database.

=AND(OrderID<>””,COUNTIF(PartsData!C:C,OrderID)>0)

If the Order ID is found in the database, the result is TRUE, otherwise the result is FALSE.

Warning Message

If you try to add an Order ID that already exists in the database, you’ll see a warning message, shown below.

  • Click Yes, to update the record
  • Or click No, enter a unique ID number, and click Add again.

dataentryform02

Message – Not in Database

Or, if you try to update a record and it’s Order ID isn’t in the database, you’ll see a different warning.

  • Click Yes to add the record, instead of updating.
  • Or, click No, enter an existing ID number, and click Update again.

dataentryform03

Download the Updated Form

To download the new version of the worksheet data entry form, please visit the the Data Entry and Update Form page on the Contextures website.

The download link is near the bottom of the page.

And if you have any ideas for future improvements, please let me know!

More Articles on the Worksheet Data Entry Forms

Website pages With Sample Files

Data Entry Worksheet Form – Basic

Worksheet Data Entry Form – Enhanced

Blog Posts

Worksheet Data Entry Form in Excel

Edit Records in Excel Worksheet Data Entry Form

New Improved Excel Data Entry Form

How to Customize the Excel Data Entry Form

Delete Orders with Excel Data Entry Form
________________________

Do You Excel in the Kitchen?

These days, I don’t have to cook too often, but company is coming tonight. So, I dug out a slow cooker recipe, and got ready to brown the meat.

My Excel Apron

Of course, I didn’t want to end up with oil spattered all over my work clothes (shorts and a t-shirt), so I grabbed the apron that my daughter made for me.

As you can see in the picture below, this is proof that I’m an EXCEL-lent cook, but not very good at ironing.

My EXCEL-lent Cook apron
My EXCEL-lent Cook apron

Excel Meal Planner

If you want to Excel in the kitchen, but don’t have a fancy apron like this one, you can use my Excel meal planner.

It includes a recipe selector tool, thanks to Jimmy Peña, so you can search the web for recipes.

Then, pick your meals for the week, and print the shopping list for those meals.

excelmealplanner01

Holiday Dinner Planner

For special occasions, like Canada Day, or July 4th, you can use the Excel holiday dinner planner to organize a meal for a large group, or a fancy dinner for friends.

How long does it take to barbeque a package of hot dogs, and defrost an apple pie?

Dinner on the Stove

And in case you were wondering, here is the dinner that I was browning, when I had to grab that apron.

It smells great, or should I say “Excellent!”, as it simmers in the slow cooker, so I hope the company enjoys it.

Dinner on the Stove
Dinner on the Stove

___________________

How to Collect Excel Tips in Pinterest

Unless you’ve been living in a cubicle, buried under a pile of budgeting worksheets, for the past year, you’ve heard about the Pinterest website.

Some people think that it’s a site for the ladies only, where you can plan a wedding, drool over food photos and learn crafty things.

Well, those people are wrong! Weddings, food and crafts make up less than 97% of the Pinterest content. 😉

Ignore the Fluff

The good news is that you can ignore all the cute, fluffy stuff, and use Pinterest to visually organize your own interests – like Excel tips.

Then, when you want to find a tip later, you can scan your Pinterest board, to find the tip’s screen shot. Click on it, to go to the original Excel tip article.

pinterestexcel01

New Way to Bookmark

In the old days, you could bookmark a website in your browser, then try to find it again, by looking through a long list of web browser bookmarks. And that only worked if you remembered the name of the site that you were looking for.

I find it much easier to spot an image in Pinterest, than the name of an obscure blog post in my Firefox bookmarks.

How to Get Started in Pinterest

You’ll have to request an invitation to join Pinterest, but it shouldn’t take long to get the confirmation email. And they obviously aren’t too picky about who joins, because they let me in!

Once you have your invitation, register at the site, and check out their Getting Started page, for the basic instructions.

Set Up Your Excel Tip Boards

When you join Pinterest, there are some default categories that you can use to group your pinned items. It’s strange, but there aren’t any default Excel categories, so you’ll have to create your own.

You can group everything in one category, such as “Excel Tips” or break them into narrower groups, like “Excel Charts” and “Awesome Pivot Tables”.

pinterestexcel02

Pin Items to Your Excel Boards

Once you have your Pinterest boards set up, you can start adding items to them. I use the Pinterest bookmarklet, which makes pinning easy.

To get it, go to the Pinterest Goodies page, and scroll to the top. Drag the Pin It button to the bookmark toolbar in your web browser. I use Firefox, so the steps might be different in another browser.

iPhone App

You can also get a Pinterest app for your iPhone, and pin items that catch your eye while you’re out and about.

Then, when you find an Excel tip that you want to pin, click the Pin It bookmarklet.

  • If you aren’t logged in, you’ll see the Pinterest login screen first.
  • Then it will show all the images from the current page, and you can click on the image that you want to pin.

pinterestexcel04

  • Select a category, enter a description, and click the Pin button.
  • You’ll see the pinned item in the board that you selected, the next time you check.

pinterestexcel05

Follow Other Excel Tip Boards

Part of the fun of Pinterest is seeing what other people have pinned. You can follow a person, and see all their pins, or follow specific boards that you’re interested in.

For example, if I search for Microsoft, in the People category, the Microsoft Cloud Services board comes up. I’m not interested in all their postings, but I’ll click “Follow” for the Office 365 board.

pinterestexcel03

Contextures on Pinterest

I’ve set up a Contextures page on Pinterest, where I’ve got boards with different types of Excel tips.

You can follow my Contextures account, or just pick a few of the boards to follow, such as Excel Charts or Pivot Table Tips.

And if you start an Excel board, please let me know.

pinterestexcel02
______________