Displaying a Worksheet from a Drop-Down List Choice

By creatively combining data validation with the HYPERLINK function, you can set up an easy way to jump around a workbook. This tip shows how easy it is to implement this technique.

Read this tip »

(Thanks to Ryszard Raciborski, Alan Cannon, Steve Aprahamian, Michael Avidan (MVP), David Watssman, and Andrija Vrcan for contributing to this tip.)

Maintaining the Active Cell

Move from one worksheet to another, and Excel selects whatever cell was last used in the worksheet you are selecting. If you don't want this behavior (you want to have the same cell selected on the new worksheet as on the old), then you can apply the techniques in this tip.

Read this tip »

Macros! Macros! Get Your Macros Here!

Learn all the best methods for creating and using Excel macros. ExcelTips: The Macros is the best single source for learning about macros or improving your existing skills. Be the person everyone looks to when something needs to happen in Excel. Get yours today!

Zooming In On Your Worksheet

If you have trouble seeing the information presented in a worksheet, you can use Excel's zooming capabilities to ease the trouble. Here's a quick discussion of all the ways you can adjust the size at which information is displayed on-screen.

Read this tip »

Breaking Up Variable-Length Part Numbers

Part numbers can often be long, made up of other component elements. Breaking up part numbers into individual components is easy if each component is a particular length. The task becomes somewhat more difficult if the components can be variable lengths. This tip presents a variety of methods of extracting variable-length component elements of a part number when those components follow a discernable pattern.

Read this tip »

This section is for those having problems making Excel behave. If Excel is giving you fits, feel free to submit your own Help Wanted question.

If you have a solution for the problems below, click the link after the problem to send us your answer. (All responses become the sole property of Sharon Parq Associates, Inc., and can be used in any way deemed appropriate.) If your response is used in a future issue, you will be credited for your contribution to the answer.

Aggregating Monthly Sales from a Larger Data Set

I have a worksheet that contains our sales transactions for the past two years. The data is imported daily from an external reporting system. The data is simple; column A is the date and column B is the amount. On a different worksheet, I would like to create a small table that represents aggregate sales for just the past six calendar months. Column A would be the month, column B would be the count of sales, and column C the sum of sales. I know I could do this with a PivotTable, but I would prefer to do it using formulas, if possible. That way I can include the aggregate data in a larger report I'm putting together.
—Tonya Pelson (provide an answer for this Help Wanted question)