Excel Outside the Box E-Book

MrExcel's Holy Macro! Books

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

Seitenzahl: 109

Veröffentlichungsjahr: 2024

Beschreibung

This book offers Excel insights for users looking to expand their expertise and tackle challenges with creativity. Whether dealing with tricky formulas, managing large datasets, or automating tasks with VBA, the content caters to both intermediate and advanced Excel users. Each chapter delves into specific aspects of Excel, such as innovative formula applications, leveraging pivot tables, and creating dynamic charts that adapt to evolving data.
The book offers a unique perspective, introducing problem-solving techniques that go beyond traditional methods. It emphasizes practical scenarios, like alphabetizing without sorting, comparing lists efficiently, or parsing complex information without losing data integrity. With dedicated sections on array formulas and VBA, readers will discover how to harness Excel's full potential for automation and advanced analytics.
The author’s approach is engaging and intuitive, ensuring that even challenging concepts like advanced array formulas or creating macros are broken down into manageable steps. The final chapters address miscellaneous tips, such as refining workflows or understanding date functions, rounding off a comprehensive guide that equips readers with tools to excel in their personal and professional data tasks.

Leseprobe

by Excel MVP

Bob Umlas

Holy Macro! Books

PO Box 82, Uniontown, OH 44685

Excel Outside the Box

© 2012 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 known to be trademarks 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 no warranty or fitness is implied. The information is provided on an “as is” basis. The authors 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.

First Printing: January 2012

Author: Bob Umlas

Technical Editor: Joseph Sorrenti

Copy Editor: Tyler Nash

Layout: Nellie J. Liwam

Cover Design: Shannon Mattiza, 6Ft4 Productions

Publisher: Bill Jelen

Indexer: Samuel T. Sharkowski

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

Distributed by Independent Publishers Group, Chicago IL

ISBN 978-1-61547-010-5 print, 978-1-61547-303-8 ePub

Library of Congress Control Number: 2011937995

Table of Contents

Introduction

Chapter 1 - Techniques

1-A Dilemma with Relative References

2-Build a Formula with a Formula

3-Combining a List of Values into One

4-Comparing Worksheets

5-Creating a Series of the 15th of the Month and the Last Day of the Month

6-Match Colors

7-Reorganization of Data

8-Scrolling Text

9-Sequences

10-Variable Linked Cell

Chapter 2 - Pivot Tables

11-Pivot Table Anomaly and Workaround

12-Reverse Pivot Table

13-Conditional Formatting

14-Data Validation on Steroids

15-The “Justify” Command

16-Unusual Text to Columns

Chapter 4 - Formulas

17-Interesting Formula using MATCH and OFFSET

18-Using LARGE (or SMALL, etc) on Non-contiguous Ranges

19-Using SUMPRODUCT for Filtered or Hidden rows

20-VLOOKUP with Multiple Answers

21-Reversing a list

Chapter 5 - Array Formulas

22-Alphabetizing without Sorting (an Array-Formula)

23-An Odd Combination

24-Another Array-Formula Application – Finding the Latest Date from a String with Embedded Dates

25-Calendar with One Formula (Array Entered, of Course!)

26-Comparing Lists

27-Finding the Last Cell Using a Formula

28-Getting Sums from a Text String

29-Parsing Information Without Breaking in the Middle of a Word (an Array Formula)

30-Using an Array Formula to Extract a Part Number

31-VLOOKUP with Multiple Answers – Another Look

Chapter 6 - Charts

32-Dynamic Range Names and Charts

33-More Dynamic Charting

34-Show a Chart Only When the Data is Complete

Chapter 7 - VBA

35-Ensure a Cell Contains a Value Before Saving File

36-A VBA User-Defined Function for Getting Sums from a Text String

37-Delete Defined Names in One VBA Statement

38-Ensuring Macros are Enabled

39-Getting a List of Sheet Tabs in a “Table of Contents”

40-Inserting Totals with VBA

41-Limiting Cells a Person Can Select Without Protecting the Worksheet

42-Many Buttons, One VBA Procedure

43-Multiple Matches

44-Overriding Cell Calculations

45-Quick Way to Put Sequential Numbers into a Range Using VBA

46-Trimming and Cleaning all Cells on a Worksheet

47-Using a Demo of a File – Not Allowing it to be Used More than X Times

48-Using Excel Ranges With the VBA Join Method

Chapter 8 - Miscellaneous

49-What Really is the Issue with Dates?

50-Getting Sums From a Text String, Revisited

51-Useful Filtering Technique

52-Referencing in Long Worksheets

53-Interesting Conditional Formatting

54-Highlighting Cells Which are Values Which Should Be Formulas, Without Using VBA

55-A Piece of Silliness

About the Author

Bob Umlas works for a major tax and accounting firm, using Microsoft Excel® eight hours a day, writing custom applications for staff and clients.

He has been using Excel since version 0.99 (on the Macintosh)! He was a contributing editor to Inside Microsoft Excel for many years, a magazine devoted exclusively to Microsoft Excel and published by The Cobb Group and later Ziff-Davis. At the time, most issues contained either an article by Mr. Umlas on using VBA (Visual Basic for Applications) or some tip or technique from him on using Excel. He has had more than 300 articles published on subjects ranging from beginner to advanced macros, and on tips, shortcuts, and general techniques using virtually every aspect of Excel.

Mr. Umlas was voted an “MVP” (Most Valuable Professional) by Microsoft each year since 1995 for his contributions to the various online Forums about Excel, and is known world-wide for his contributions in Excel. As an MVP, he meets yearly with his fellow-MVPs at Microsoft’s headquarters in Redmond, WA, where he has access to the product developers. He has been a beta tester for new versions of Excel since version 1.5, and was asked by Microsoft for his input for newer versions of Excel. In 1995 he led a session called “Maximizing Excel Development Using Array Formulas” at Microsoft’s Tech Ed Conference in New Orleans, and he led a session called Tips and Tricks at a Microsoft convention in New York City. He has also led two Excel sessions (Array Formulas, Tips & Tricks) at the Advisor’s Developer Conference in San Francisco in February 1998.

Most recently, he led five sessions at an Excel User Conference in Atlantic City on Tips & Tricks, Array formulas, VBA, Formulas, and Userforms. He has led about seven or eight of these user conferences since 2005.

He is also the author of “This isn’t Excel, it’s Magic!” which is available from http://www.iil.com/publishing as well as from Amazon.com. (There are two versions of this book: One for Excel 2003, and one for Excel 2007).

He has co-authored several chapters in many books on Excel and has done the technical editing for six new books for Excel 2010 and has a white paper on array formulas published at http://www.emailoffice.com/excel/arrays-bobumlas.html .

Mr. Umlas used to co-lead the New York PC User’s group on Excel every month for about 10 years. He has been teaching Excel to individuals and corporations for several years. Currently, Mr. Umlas leads a 12-hour class in Excel called Excel in Depth and a 6-hour class on VBA(see http://www.iil.com, click on “Virtual Classroom”,then click on Virtual Classroom Schedule, choose Microsoft® Excel in Depths, click “See your selection”, click “Course Outline”).

Comments from readers:

“This book from the Excels Guru’s Guru Bob Umlas will satisfy the Excel superstar in every office. Master tricks that mortals would think are impossible with Excel. The formulas in this book will solve every bizarre Excel problem.”     – Bill Jelen, MrExcel.com, Excel MVP

“The hidden power of Excel with excellent examples, expertly explained by Bob Umlas” – Roger Govier, Excel MVP

“A book full of ingenuity” – Bill Manville, Excel MVP

“Seemingly impossible Excel challenges made simple! Another excellent book for the intermediate to advanced Excel addict, by Excel emperor Bob Umlas” – Jan Karel Pieterse, Excel MVP

Acknowledgments

I want to thank my fellow Excel MVP folks who were kind enough to read through this manuscript and make corrections, suggestions, etc. So thanks to Dick Kusleika, Jon Peltier, Richard Schollar, Ron de Bruin, Rick Rothstein, Jan Karel Pieterse, Roger Govier, Niek Otten, Bill Manville, Greg Truby, and especially Bill Jelen. I also want to thank my “official” technical editor and coworker, Joseph Sorrenti for his willingness to read through all the material and make suggestions for improvements and additions, as well as finding some typos!

I want to thank my wife for all her support of my taking the time (our time) to write this book as well as her unending love. She’s an author herself (The Power of Acknowledgment), so she knows what goes into the creation of a book.

I also want to thank the Microsoft MVP program without which I would not have met such knowledgeable others to give a “second look” at the materials presented here.

Dedication

I want to dedicate the book to my family (wife Judy, son Jared, daughter Stefanie, son-in-law Shaun, granddaughter Lilith) and also to my fellow MVPs.

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. 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 book (“This isn’t Excel, it’s Magic”), I thought it was time for another book to help the already-advanced Excel developer get past these hurdles.

So many times I’ve seen a data validation list contain many blanks at the bottom of the list (which was fixed in Excel 2010!), or with gaps, like this:

when it should look like this:

Even if the source contains those gaps. I describe how to fix that (and so many more things) in this book.

I show a lot of involved formulas but I decipher them step by step so they’re no longer so formidable, like this array-entered one, for example:

=INDEX(A:A,SMALL(IF(ISNA(MATCH($A$1:$A$12,$C$1:$C$9,0)),ROW($1:$12),””),ROW(A2)))

This book is aimed at the company’s already-expert Exceller, elevating him/her to the next level.

–Bob Umlas

Chapter 1 - Techniques

1-A Dilemma with Relative References

Figure 1 and Figure 2 show a simple worksheet normally and with formulas showing:

Figure 1

Figure 2

Assume this goes on for hundreds of rows.

Also assume you discovered that most of the formulas are wrong – you really wanted those cells which refer to Data to be 3 rows down. That is, in cell D1 you wanted the reference to be to cell M20, not M17. How can you fix it? There’s nothing to replace – replacing 17 with 20 certainly won’t help. You can’t fix the first one and fill down because of the cells which are not of a like formula – for example, filling down would destroy the formula in cell D4. Figure 3 is what you want, how can you get there?

Figure 3

The answer (well, one answer, anyway) is to switch to R1C1 format. Yes, it actually does have a really good use! It’s done here in the File menu, Options.

Figure 4

How does that help? Look at the underlying formulas here:

Figure 5

See those [16]’s? All you need to do is change them to [19]’s, with the result shown below:

Figure 6

When you switch back to A1 notation (uncheck R1C1) you have this:

Figure 7

And you’re done! But wait – here’s an entirely different approach which also works well. First, create a new sheet, say that’s Sheet4. It will remain empty for this process, but will serve an important function as you will soon see. Change the above formulas by changing the reference from Data to Sheet4, as seen here:

Figure 8

And afterwards as you see here:

Figure 9

Now go to Sheet4, select cells M1:M3, Insert cells (Home tab), shifting down:

Figure 10

Return to the main sheet and look at the formulas:

Figure 11

Now change Sheet4 back to Data and you’re done:

Figure 12

2-Build a Formula with a Formula

Look at the worksheet in Figure 13 on the next page.

You can see that some information from Sheet2 is being picked up on Sheet1 via an INDIRECT formula. A quick look at Sheet2 (nothing special) in Figure 14.

Why use INDIRECT? Perhaps the information on Sheet2 may be cut/pasted elsewhere, but you are interested in the data in A1:A22, regardless of how information may be moved around.

What’s the issue? Look at the formulas in Sheet1 in Figure 15.

The problem is, how can you create these formulas without typing each one (or without reverting to creating a VBA procedure!)? You can’t fill the formula down from A1, because it’s all text, and the formula will stay the same. The answer lies in building the formula with a formula. Let’s see what this means (Figure 16).

Notice that the formulas in column A seem to be the same as the previous screenshot, but look at what’s in the formula bar! You are looking at =”=INDIRECT(“”Sheet2!A”&ROW(A1)&”””)” which is the formula you want to be in cell A1, but entirely built as a string except the reference to ROW(A1) to give us the one you want.

Figure 13

Figure 14

Figure 15

Figure 16

Figure 17

becomes

Figure 18

and then

Figure 19

becomes

Figure 20

This becomes what you see in cell A1. Now when this formula is filled down, the ROW(A1) becomes ROW(A2), etc. and you have what you need. However, this is not ready for use – it’s not an INDIRECT formula, it’s a text string containing