More Excel Outside the Box E-Book

MrExcel's Holy Macro! Books

• Herausgeber: Packt Publishing
• Kategorie: Lebensstil
• Sprache: Englisch

Seitenzahl: 65

Veröffentlichungsjahr: 2024

Beschreibung

This book is a treasure trove of innovative techniques for Excel enthusiasts, starting with dynamic user interface tips like using conditional formatting switches, data validation to restrict input, and creating adaptive workflows without relying on VBA.
As you progress, you'll explore powerful array formulas to solve complex challenges, such as finding prime numbers, handling variable ranges, extracting numbers from text, and more. The VBA section dives deeper, introducing ways to automate tasks, manage user forms, and create dynamic solutions like filtering data, filling lists, or building single-click summary charts.
The final chapters are a mix of advanced tricks, including crafting a Gantt chart with data from multiple sources, safeguarding tables, and uncovering lesser-known Excel features. Each topic is presented with clear instructions, real-world examples, and expert advice, making it an essential resource for power users aiming to maximize Excel’s capabilities. Whether you're solving daily tasks or tackling advanced projects, this book equips you with the tools to excel.

Leseprobe

by

Bob Umlas

Holy Macro! Books

PO Box 82

Uniontown OH 44685

More Excel Outside the Box

© 2015 by Bob Umlas and Holy Macro! Books

All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information or storage retrieval system without written permission from the publisher.

All terms known in this book to be trademarked have been appropriately capitalized. Trademarks are the property of their respective owners and are not affiliated with Holy Macro! Books.

Every effort has been made to make this book as complete and accurate as possible, but not warranty or fitness is implied. The information is provide on an “as is” basis. The author and the publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book.

Printed in the USA by Hess Print Solutions

First Printing: May 2015

Author: Bob Umlas

Cover Design: Shannon Mattiza 6Ft4 Productions

Publisher: Bill Jelen

Index: Nellie Jay

Proofreader: Kurt Nichols

Published by Holy Macro! Books, PO Box 82, Uniontown OH 44685

Distributed by Independent Publishers Group, Chicago IL

ISBN 978-1-615470-036-5

Library of Congress Control Number: 2015940631

Table of Contents

Introduction

User Interface Topics

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

Array Formula Topics

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

VBA Topics

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

Miscellaneous Topics

Creating a Gantt Chart from Many Sources (on Steroids!)

Protecting Tables of Data

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

Index

About the Author, Bob Umlas

Other books by Bob Umlas:

This isn’t Excel, it’s Magic (2005)

This isn’t Excel, it’s Magic 2nd Edition (2007)

Excel Outside the Box (2012)

Introduction

There are many Excel books out there; why did I write this one? My daily work involves writing custom applications using Excel and there are many problems I’ve had to think through to come up with a solution in Excel. My previous book, Excel Outside the Box, tackled many of these problems, and I came up against several more – enough to write another book!

I’ve used these solutions many times in my applications. I have not found any books or articles which described similar issues and solutions, yet it is hard to imagine that Excel developers in other companies hadn’t also come up against these issues. So, given the feedback from my previous version of this book, I thought it was time for another book to help the already-advanced Excel developer get past these hurdles.

User Interface Topics

This section explores many ways to validate data, rearrange data, change how data appears, etc. in ways likely not seen before. It explores Conditional Formatting, formulas, some VBA, and more techniques which you will find interesting.

Using a Conditional Formatting On/Off Switch

This worksheet uses conditional formatting to highlight each cell that contains a value over 50:

There are several ways to get this kind of conditional formatting. One way is to use Highlight Cells Rules, as shown here:

The worksheet above uses a formula, like this:

This type of conditional formatting can be really helpful, but you might want to be able to turn it on and off in a worksheet. In that case, you could put a data validation switch in cell I1, like this:

Then you could change the formula to this:

Your worksheet would then look like this with the conditional formatting turned on:

And it would look like this with the conditional formatting turned off:

(The numbers keep changing here because the RAND function is used in the formulas in all the cells.)

Using Data Validation to Prohibit Entry of Digits

This figure shows an invalid entry error:

This isn’t a valid entry because there are digits in cell A1, and the worksheet is set up to allow only letters. To avoid getting this type of error, you can use data validation.

A data validation formula is a custom formula that appears in the Data Validation dialog:

Here’s the complete formula:

=NOT(ISNUMBER(MATCH(FALSE,ISERROR(1*MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)),0)))

Normally, as a worksheet function, this would need to be entered as an array (using Ctrl+Shift+Enter), but you can’t enter a formula that way in the Data Validation dialog. Therefore, Excel treats any formula you enter as if it were an array formula.

This formula examines each character in cell A1 and multiplies it by 1 to determine whether it’s a number. If it is a number, then it shouldn’t be allowed.

Let’s examine how this works with the ABC123 entry from the inside out (using F9 for each step):

becomes

becomes .

becomes .

becomes (note the 1;2;3 at the end).

becomes .

becomes 4; and becomes , which becomes FALSE.

Therefore, the data validation kicks out the value as invalid because the data validation formula is FALSE.

Let’s redo this examination, with A1 containing the text Hello World:

becomes .

becomes .

becomes .

becomes .

becomes .

becomes .

Therefore, Hello World is a valid entry.

Making a Change in One Column Based on Criteria in Another (Without VBA)

Consider this Figure:

Someone recently asked me how he could change the agent names in column A to “Internet” whenever column B contained either Hotels.com or Booking.com.

One way to do this would be to use a helper column: You could simply use an if-statement to test for either value and use “Internet” if TRUE or the current value in column A if FALSE; then you would copy the helper column and paste values into column A and then clear the helper column.

But this person didn’t want to use a helper column—or VBA.

I thought about the problem a bit and came up with another way to approach it. I added a filter to the range and then select Hotels.com and Booking.com from column B:

This yields the following:

Next, I selected from A2 to the bottom and pressed Alt+; (the equivalent of selecting Go To Special/Visible Cells Only):

Next, I typed Internet and pressed Ctrl+Enter:

When I cleared the filter, I saw that I was done:

Changing Row Height When a Key Field Changes

Look at this figure, which is part of a worksheet that continues for hundreds of rows:

Say that the important column here is D, and you want to make the worksheet group together the rows with the same territory field, like this:

In this case, rows 8 and 17 have a different height than the other rows, which provides some visual grouping for the territory entries.