Excel Gurus Gone Wild: Do the IMPOSSIBLE with Microsoft Excel

FRONT COVER......Page 1
TABLE OF CONTENTS......Page 9
ABOUT THE AUTHOR ACKNOWLEDGEMENTS......Page 4
FOREWORD......Page 8
DEDICATION......Page 7
PART 1......Page 12
FIND THE FIRST NON-BLANK VALUE IN A ROW......Page 13
CALCULATE WORKDAYS FOR 5, 6, 7 DAY WORKWEEKS......Page 15
STORE HOLIDAYS IN A NAMED RANGE......Page 20
SUM EVERY OTHER ROW OR EVERY THIRD ROW......Page 21
WHY THE MINUS MINUS? COERCE NUMBERS FROM TRUE/ FALSE......Page 23
INTRODUCING THE BORING USE OF SUMPRODUCT......Page 26
UNDERSTAND BOOLEAN LOGIC: FALSE IS ZERO; AND IS *,OR IS + AND EVERYTHING ELSE IS TRUE......Page 27
USE GET.CELL TO HIGHLIGHT NON-FORMULA CELLS......Page 31
REFER TO A CELL WHOSE ADDRESS VARIES, BASED ON A CALCULATION......Page 38
POINT TO ANOTHER WORKSHEET WITH INDIRECT......Page 41
GET DATA FROM ANOTHER WORKSHEET BY USING INDIRECT......Page 45
USE INDIRECT TO GET A DATA FROM A MULTI-CELL RANGE......Page 47
ALWAYS POINT TO CELL B10......Page 48
USE NATURAL LANGUAGE FORMULAS WITHOUT USING NATURAL LANGUAGE FORMULAS......Page 49
SUM A CELL THROUGH SEVERAL WORKSHEETS......Page 50
SUM VISIBLE ROWS......Page 51
LEARN R1C1 REFERENCES......Page 54
RANDOM NUMBERS WITHOUT DUPLICATES......Page 57
SORT WITH A FORMULA......Page 60
DEAL WITH DATES BEFORE 1900......Page 62
USE VLOOKUP TO GET THE NTH MATCH......Page 63
USE A SELF-REFERENCING FORMULA......Page 66
USE TWO-WAY INTERPOLATION WITH A SINGLE FORMULA......Page 68
FIND THE SUM OF ALL DIGITS OCCURING IN A STRING......Page 72
GET AN ARRAY OF UNIQUE VALUES FROM A LIST......Page 73
AUTO-NUMBER RECORDS AND COLUMNS IN AN EXCEL DATABASE......Page 76
PART 2......Page 80
USE AUTOFILTER WITH A PIVOT TABLE......Page 81
SORT SUBTOTALS......Page 82
COPY AN EXACT FORMULA BY USING DITTO MARKS......Page 83
RIGHT-DRAG BORDER TO ACCESS MORE COPYING OPTIONS......Page 84
QUICKLY CREATE A HYPERLINK MENU......Page 86
QUICKLY CREATE MANY RANGE NAMES......Page 87
ADD FORMULAS TO SMARTART......Page 90
CREATE A PIVOT TABLE FROM DATA IN MULTIPLE WORKSHEETS......Page 93
DETERMINE THE HEIGHT AND WIDTH OF THE DATALABEL OBJECT......Page 96
ADJUST XY CHART SCALING FOR CORRECT ASPECT RATIO......Page 99
PART 3......Page 106
MAKE A PERSONAL MACRO WORKBOOK......Page 107
RUN A MACRO FROM A SHORTCUT KEY......Page 111
RUN A MACRO FROM A BUTTON......Page 113
RUN A MACRO FROM AN ICON......Page 120
CREATE A REGULAR MACRO......Page 126
CREATE AN EVENT HANDLER MACRO......Page 127
EXTRACT AN E-MAIL ADDRESS FROM A CELL CONTAINING OTHER TEXT......Page 129
FIND THE CLOSEST MATCH......Page 132
USE TIMER TO MICRO-TIME EVENTS......Page 134
DISCOVER THE TEMP FOLDER PATH......Page 136
USE EVALUATE IN VBA INSTEAD OF LOOPING THROUGH CELLS......Page 138
RENAME EACH WORKSHEET BASED ON ITS A1 VALUE......Page 140
USE A CUSTOM PULL FUNCTION INSTEAD OF INDIRECT WITH A CLOSED WORKBOOK......Page 141
IN VBA, DETERMINE THE NUMBER OF THE ACTIVE WORK-SHEET......Page 142
CREATE WORKSHEET NAMES BY USING THE FILL HANDLE......Page 143
COPY THE PERSONAL MACRO WORKBOOK TO ANOTHER COMPUTER......Page 147
ADD FILTER TO SELECTION FUNCTIONALITY......Page 149
USE A MACRO TO HIGHLIGHT THE ACTIVE CELL......Page 153
REMOVE THE CAPTION BAR FROM A USER FORM......Page 156
KEEP A BUTTON IN VIEW......Page 158
ADD A RIGHT-CLICK MENU TO A USER FORM......Page 159
FORMAT A USER FORM TEXT BOX AS CURRENCY OR A PERCENTAGE......Page 163
DELETE RECORDS IN VBA......Page 165
SELF-SIGN YOUR MACROS FOR CO-WORKERS......Page 167
MAGNIFY A SECTION OF YOUR SCREEN......Page 171
LIST COMBINATIONS OF N ITEMS TAKEN M AT A TIME......Page 172
APPENDIX 1 - ALPHABETICAL FUNCTION REFERENCE......Page 178
INDEX......Page 244