More Excel Outside the Box

Unbelievable Excel Techniques from Excel MVP Bob Umlas

Bob Umlas

• Publisher: Holy Macro! Books
• Year: 2015
• Print ISBN: 9781615470365
• eBook ISBN: 9781615473441
• Edition: 1
• Topic: Computer Science
• Subtopic: Desktop Applications
• Index: Computer Science

A follow-up to Excel Outside the Box, More Excel Outside the Box is designed with the Excel guru in mind, introducing advanced, creative solutions and hacks for the software's most challenging problems. Through a series of more than 30 techniques, tables, formulas, and charts, this guide details processes that may be used in any Excel application and across all disciplines. Creative approaches for building formulas within formulas, pivot tables, conditional formatting, and mastering array formulas are just some of the numerous challenges explained. Other higher-level solutions discussed include using VBA macro code to override cell calculations, solve for sums from a text string, and trimming and cleaning all cells on a worksheet. This is the all-encompassing resource for advanced users of Excel wanting to learn more techniques to broaden and empower their knowledge of the program.

Did You Know…? (a potpourri of miscellaneous ideas)

Going to the Previous Cursor Position in VBA

Did you know that VBA enables you to go to the previous cursor position? For example, here’s a short piece of code in which one of the statements references another routine. Without even running the code, you can right-click next to or inside the text AnotherRoutine and see where it’s defined:

Here’s the procedure (which is in a different module):

Notice that the cursor is blinking in the line directly under Sub. How do you get back to where you were? Notice the Last Position command in the figure above, right under Definition. If you issue that command now:

You wind up back in the place where you issued the Definition command. If the cursor has been in many places, you can issue Last Position repeatedly to trace the path of the various places you’ve visited.

Going Directly to a VBA Procedure

You can get to a VBA procedure directly, while not in the VBE, from Excel’s Go To or Name box. F...

Table of contents

• Introduction
• Using a Conditional Formatting On/Off Switch
• Using Data Validation to Prohibit Entry of Digits
• Making a Change in One Column Based on Criteria in Another (Without VBA)
• Changing Row Height When a Key Field Changes
• Using a Nontrivial Conditional Formatting Formula
• Rearranging Data (Revisiting a Technique from Excel Outside the Box)
• Truncating Text and Showing an Ellipsis (…) After 47 Characters
• Copying Cells Without the Blank Rows
• Alternating Conditional Formatting
• Using Relative Names
• Merging Across
• Using Wildcards in Replace Formula
• Finding Prime Numbers
• Easily Clearing an Array-Entered Block of Cells
• Determining Whether a Cell Contains a Word from a List of Words
• Using Variable Ranges for Unique Counts
• Extracting Numbers from Text
• Partially Matching Words with a User-Defined Function
• Setting Up Many Check Boxes on a Worksheet
• Narrowing Down What Caused a Crash
• Filling a List Box with Months
• Filtering by Data in a Text Box
• Creating a Summary Chart with a Single Click
• Copying Modules or User Forms from One Project to Another
• Using the Locals Window in VBA
• Creating a Gantt Chart from Many Sources (on Steroids!)
• Protecting Tables of Data
• Did You Know…? (a potpourri of miscellaneous ideas)
• About the Author, Bob Umlas