Elvis Sings Excel: A Little Less Concatenation

Last Friday, January 8th, would have been Elvis Presley's 75th birthday. Sadly, he died in 1977, so he never had a chance to work with Microsoft Excel. Otherwise, he might have sung "A Little Less Concatenation", instead of "A Little Less Conversation."

Continue reading "Elvis Sings Excel: A Little Less Concatenation"

Happy New Year in Excel

Happy New Year! All the best in 2010, and I hope you're looking forward to the release of Office 2010.
I thought about creating some fireworks in Excel, then discovered that Andy Pope already made some, based on an xy scatter chart. Here's a screen shot that shows one of the bursts. And there's no noise, so if your head hurts a little today, the fireworks won't make it worse.
Andy also has a sample Excel workbook with scrolling text, so you can combine the workbooks to show Happy New Year, and a fireworks display.

Print Selected Items on Excel Invoice

You have an Excel order form with a long list of products to select. After a customer enters quantities for the products they want to order, you want to print an invoice for the selected items.

Select the Products

Here's the OrderForm sheet, where quantities have been entered for four of the products, in column E.
Formulas in column B check the quantity column (E). If the quantity is filled in, then the next available number is calculated.

Print the Order

On the Invoice sheet, formulas pull the data from the Order form. For example, this formula shows the name of the first product that was ordered:

Here's the Order Form sheet, ready to be printed. The numbered rows from the Products sheet have been added to the Order form. Products without a quantity, such as Sweater, don't appear.

The Sample File

To download the sample file, please visit the Excel Sample Files page, on my Contextures website. In the Functions section, look for FN0027 - Print Marked Invoice Items.

Numbered Excel Sheets Cause Problems

Naming Excel sheets – how hard can it be? Last month, we looked at sheet names, and saw the problems that were caused by using an apostrophe. This week, I found another sheet name problem.
Someone, let's call him Mr. X, contacted me about one of my sample worksheets -- Create New Sheets from Filtered List. The file has a macro that creates individual sheets for each sales rep, filtering data from a master sheet, like the one shown below.

Second Time Around

Mr. X said he could run the macro once, then got an error the next time he tried to run it. His email also included those dreaded words, "I made some changes." Unfortunately, there was no clue as to what those changes were, but at least he attached the problem file. Instead of Rep names, he wanted to created a worksheet for each customer ID.
Sure enough, the macro created all the sheets, without problems, the first time it ran.
Then, as promised, I got an error the second time that it ran.
And mysteriously, the Data sheet had been cleared.

The Numbers Game

What happened? Well, the code runs though the list of Customer ID numbers, and if a sheet doesn't exist for that number, it creates one. If the sheet does exist, it clears the sheet, then adds the new data.
When the code got to customer ID 1, instead of clearing the "1" sheet, it cleared the first sheet, Data, which has an index number of 1.
To fix the problem, I changed the c.Value reference in the code to CStr(c.Value)) and instead of looking for the sheet with an index number of 1, it looked for a sheet with a name of "1".

Watch the Numbers

If you use numbers as sheet names, such as a sheet for each day of the month, be careful how you refer to those sheets in your code. You don't want to end up like Mr. X, with blank sheets, and error messages!

Freeze Panes to Hide Rows in Excel

For the past 82 years (approximately), I've used Excel almost every day. Along the way, I've learned a few tricks, and produced some sophisticated workbooks. One of the joys of Excel is that there's always something new to learn. And this week I learned how to freeze panes to hide rows in Excel.

Continue reading "Freeze Panes to Hide Rows in Excel"

Your Sheet Names Are Killing My Formulas

Have you read all the Excel horror stories and costume ideas in the Very Scary Fall Giveaway for Excel Nerds? There's some truly frightening stuff there! The entry deadline is tomorrow at noon, Eastern Daylight Time, so get moving if you haven't entered already.

My Horror Story

One of my Excel horror stories involves sheet names. I set up a client's workbook with pre-formatted data entry sheets, so sales managers could plan their annual product promotions. They would rename the sheets while working, to make it easier to navigate the completed workbook.
On a hidden summary sheet, I added formulas to calculate the sheet names. Then, INDIRECT formulas pulled data from specific cells on each sheet, and other formulas created grand totals. At the front of the workbook, the summary data was displayed in a monthly calendar, for sales managers to review. It was a work of art!

The Scary Phone Call

Everything worked well in testing, so we distributed the files to all the sales managers, and they started filling in their data. The next day, the phone rang – some of the workbooks were "broken." Budget deadlines were looming, and the sales managers with broken files were in a panic. They sent me a couple of problem files, so I could figure out what was wrong.
On the Summary sheet, some of the formulas were working correctly, but others showed #REF! errors. Comparing the good and bad sheets, I couldn't see any problems with the data that had been entered.
Finally, after checking a few of the problem sheets, I spotted a similarity. All of them included an apostrophe in the sheet name! I removed the apostrophes, and the problem was solved. All the data showed up in the summary sheets, and the world was in harmony once again.

Sheet Naming Rules

I hadn't anticipated that problem, since I never use apostrophes in sheet names. They're valid characters for a sheet name, but maybe they shouldn't be.
It's hard to find the sheet naming rules in Excel's help, but you may have seen an error message that lists them.

  1. The name can't be more than 31 characters, and you can't leave the sheet tab blank
  2. Only a few characters are invalid:

  : / ? * [ ]


Sheet Naming Suggestions

In addition to those rules, I have a couple of guidelines of my own.

  1. Use only letters, numbers and underscores in sheet names. Sometimes I have to use a space character, if a client requests specific sheet names, but I try to avoid it. For example, I'd use SalesData or Sales_Data, not Sales Data.
  2. Use different names for sheets and named ranges, to avoid confusion.

Your Sheet Naming Rules

  • What kind of names do you use for worksheets?
  • Any characters that you avoid or problems you've run into?


AutoFilter By Selection In Excel

In Excel 2003, you can add a couple of buttons to the toolbar to make it easy to filter a table. For example, in the table below, the East region is selected. With one click of a button, and no programming, you can add an AutoFilter and filter the table to show only the East region orders. Thanks to Roger Govier for sharing this tip.

Continue reading "AutoFilter By Selection In Excel"

Grab the Excel Color Palettes

While working on an Excel 2003 worksheet, you might want to colour a few of the cells, change the font colour for the text, or add borders to a range of cells.
To add colour, you select a cell, click on the dropdown arrow at the right side of the colour button on the toolbar, and click on a colour.

That's simple enough, but all those trips up and down from the toolbar can get a bit tiring, and take extra time.
To Save Time, Move the Palettes
If you have lots of formatting to do, it will be easier if the palettes are closer to the area where you're working. You can detach the palettes from the toolbar, and move them onto the worksheet.
Note: This feature is not available in Excel 2007, or later versions.
To move one of the "tear away" palettes (Font Color, Fill Color, or Borders):

  1. On the toolbar, click the dropdown arrow, to open the palette.
  2. At the top of the palette, point to the thin grey bar.
  3. When your pointer changes to a four-headed arrow, drag the palette down onto the worksheet.

When you've finished working with the palette, click the X at its top right, to close it.

And here's a short video, if you'd like to see the steps performed on screen: