• Book Name: Solutions With Excel A Users CookBook by Harish Gopalkrishnan
• Author: Harish Gopalkrishnan
• Pages: 643
• Size: 10 MB

Solutions With Excel by Harish Gopalkrishnan PDF

Contents of Solutions With Excel by Harish Gopalkrishnan PDF

CHAPTER 1 GETTING STARTED

1.1 FOR AN ANALYST/MANAGER NOT A PROGRAMMER 1.2 NEVER WRITTEN A MACRO? –PLEASE SEE THE APPENDIX 1.3 FLEXIBILITY, MAINTAINABILITY, USER-FRIENDLINESS 1.4 NO SELECTION, ACTIVEWORKSHEETS OR ACTIVATE … 1.5 USING THIS BOOK – VIEWING CODE EXAMPLES 1.6 WHAT YOU SHOULD ALREADY KNOW 1.7 WHAT IS NOT COVERED HERE Part I: Working with data in the workbook

CHAPTER 2 THE BASICS

2.1 IMPORTANT NOTES ON ORGANIZING DATA 2.2 SELECT, DELETE, INSERT ENTIRE ROW/COLUMN 2.2.1 Moving to the first non-blank column on the Right/Left 2.2.2 Moving to the first non-blank row at the Top/Bottom 2.2.3 Best way to get last row/column in a table of data 2.2.4 Working with all rows in the table with ‘for’loop 2.2.5 Some more useful keystrokes 2.3 SPECIFYING DATES IN MACROS

CHAPTER 3 A WIDE RANGE OF POSSIBILITIES

3.1 CONSTRUCTING A RANGE 3.2 FINDING VALUES WITH VBA –FIND METHOD 3.3 REPLACING VALUES WITH VBA – REPLACE METHOD 3.4 GETTING RANGE FOR TABULAR DATA (WHEN THINGS ARE FINE) 3.4.1 Top-Left Cell known, no blanks – CurrentRegion 3.4.2 Top-Left and Right Cells known, blanks present 3.5 PUTTING FORMULAS IN A WORKSHEET WITH VBA 3.5.1 Formulas referring to another workbook or worksheet 3.5.2 Applying a formula to an entire column 3.5.3 Convert formulas to values – PasteSpecial Method 3.6 USING VB ARRAYS WITH WORKSHEET RANGE 3.6.1 A word on Array bounds

CHAPTER 4 AUTOFILTER

4.1 APPLYING AUTOFILTER 4.1.1 Autofilter with keystrokes 4.1.2 Autofilter with VBA 4.1.3 Autofilter – the nitty gritty 4.1.4 Autofilter with VBA– Column positions known 4.2 WORKING WITH FILTERED CELLS – SPECIALCELLS 4.2.1 Put formula or value in one column at a time 4.2.2 Copy filtered rows to a temporary sheet 4.3 AUTOFILTER WITH VBA – COLUMN POSITIONS UNKNOWN

CHAPTER 5 PIVOTTABLES AND VBA

5.1 REFERRING TO PIVOTTABLES IN MACROS 5.1.1 Setting name of a PivotTable 5.1.2 Linking to a VBA PivotTable object 5.2 UPDATING BOUNDS OF A PIVOT TABLE IN VBA 5.3 READING DATA FROM A PIVOT TABLE WITH VBA CHAPTER 6 SORTING DATA IN A RANGE OR TABLE 6.1 SORT OBJECT OF A WORKSHEET 6.2 SORTFIELDS COLLECTION 6.3 SORTING A RANGE – COLUMN POSITION UNKNOWN

CHAPTER 7 HANDY WORKSHEET FORMULAS AND FUNCTIONS

7.1 THE POWER OF ARRAY FORMULAS… 7.2 …AND WHY WE USE THEM ONLY SPARINGLY 7.3 INDEX 7.4 MATCH 7.5 COUNTA 7.6 INDIRECT 7.7 TEXT FUNCTIONS – TRIM, CLEAN, TEXT 7.8 CONDITIONAL MATH – COUNTIFS, SUMIFS, AVERAGEIFS Scenario A – Criteria for numeric values Scenario B – Criteria for text values 7.8.1 Precautions and some useful points 7.9 OFFSET 7.10 ADDRESS 7.11 GETTING VALUES FROM TABLES (INDEX + MATCH) 7.11.1 Sample data- piping engineering 7.12 THE SAME THING DONE DIFFERENTLY (VLOOKUP + MATCH) 7.13 A BETTER USE FOR INDEX + MATCH

CHAPTER 8 HANDY VISUAL BASIC FUNCTIONS

8.1 FUNCTIONS FOR ARRAYS 8.1.1 Length of & looping through arrays 8.1.2 Checking dimensions of an array 8.2 FUNCTIONS FOR STRINGS 8.3 FUNCTIONS FOR DATES 8.4 OTHER FUNCTIONS

CHAPTER 9 EXCEL TABLES

9.1 CREATING A TABLE ON THE WORKSHEET 9.2 USING TABLE AND COLUMN NAMES IN FORMULAS 9.2.1 In Mathematical functions 9.2.2 In Lookup functions 9.3 CREATING PIVOT TABLE WITH A TABLE AS DATA SOURCE 9.4 CREATING A CHART WITH A TABLE AS A DATA SOURCE 9.5 USING TABLE AND COLUMN NAMES IN VBA

CHAPTER 10 DYNAMIC NAMED RANGES

10.1 RULES FOR CHOOSING NAMES FOR A RANGE 10.2 SELF-EXPANDING NAMED RANGES ON WORKSHEET 10.2.1 Dynamic range with a single row 10.2.2 Dealing with Layout changes 10.2.3 Dealing with our expanding business 10.2.4 Dynamic range with a single column 10.2.5 Dynamic range for tabular data 10.3 DYNAMIC NAMED RANGES IN (ARRAY) FORMULAS 10.4 USING DYNAMIC RANGES IN CHART SERIES 10.4.1 Adding a new series: 10.5 CHOOSING ONE OUT OF MANY SERIES 10.6 CHARTS WITH N-PERIOD ROLLING RANGES 10.7 COUNTRY-STATE-CITY SELECTION (CELL VALIDATION WITH DYNAMIC RANGES) 10.7.1 General method for cell validation with a named range 10.7.2 Preparing the reference data 10.7.3 Preparing the named ranges 10.8 USING NAMES IN VBA

CHAPTER 11 WORKING WITH CHARTS IN VBA

11.1 NAMING A CHART 11.2 OBJECT MODEL FOR WORKING WITH CHARTS 11.3 SAVING A CHART AS AN IMAGE FILE

CHAPTER 12 OTHER COOL STUFF

12.1 TRIGGER YOUR MACRO WHEN YOU ARE SLEEPING 12.1.1 Trigger macro in the same workbook 12.1.2 Trigger macros in multiple workbooks 12.1.3 Managing existing scheduled tasks 12.1.4 Editing the launcher workbook 12.2 ZIP AND UNZIP FILES WITH VBA 12.2.1 Creating Zip files 12.2.2 Unzipping files 12.3 UP(DOWN)LOADING FILES FROM WEB SERVERS WITH FTP

CHAPTER 13 WORKING WITH EMAIL ATTACHMENTS

13.1 WORKING WITH MICROSOFT OUTLOOK 13.1.1 Coupling Outlook to Excel 13.1.2 The Outlook Object Model 13.1.3 Read emails and download attachments 13.1.4 Send emails with attachments 13.2 WORKING WITH LOTUS NOTES 13.2.1 Coupling Notes to Excel 13.2.2 The Lotus Notes Object Model 13.2.3 Code for downloading attached Excel files 13.2.4 Code for sending mails with attached Excel files 13.3 WORKING WITH GMAIL 13.3.1 Change Gmail settings to work with Outlook 13.3.2 Setup Outlook to work with Gmail

CHAPTER 14 MANAGING FILES ON DISK

14.1 MANAGE FILES WITH VBA – FILESYSTEMOBJECT 14.1.1 FileSystemObject 14.1.2 Folder 14.1.3 Folders 14.1.4 File 14.2 LET USER SELECT FILE(S) OR FOLDER(S) – FILEDIALOG 14.3 READING & WRITING TEXT FILES – TEXTSTREAM OBJECT 14.3.1 Ways to obtain a TextStreamObject 14.3.2 Properties and Methods of TextStream 14.3.3 Code to read a text file into Excel 14.3.4 Code to write Excel data to a text file

CHAPTER 15 WORKING WITH MS WORD

15.1 EASY AND STRAIGHTFORWARD – LINKING 15.2 AN ALTERNATIVE – MAIL MERGE 15.3 BIT DIFFICULT BUT FLEXIBLE – MACROS 15.3.1 Connecting Word to Excel 15.4 THE MS WORD OBJECT MODEL 15.4.1 Application 15.4.2 Using a running Word application – GetObject Function 15.4.3 Application properties and methods 15.4.4 Documents Collection 15.4.5 Document 15.4.6 Range 15.4.7 Bookmarks collection 15.4.8 Bookmark 15.5 CREATING A BOOKMARK MANUALLY 15.6 CREATING THE BASE FILE 15.7 UPDATING THE BASE FILE USING MACROS 15.7.1 Transfer text to a specific location 15.7.2 Transfer tabular data 15.7.3 Transfer a chart 15.8 TRANSFER A TABLE WITH VARYING NUMBER OF ROWS

CHAPTER 16 WORKING WITH MS POWERPOINT

16.1 CONNECTING POWERPOINT TO EXCEL 16.2 THE MS POWERPOINT OBJECT MODEL 16.2.1 Application 16.2.2 Presentations Collection 16.2.3 Presentation 16.2.4 Slides collection 16.2.5 Slide 16.2.6 SlideRange 16.2.7 Shape 16.2.8 Shapes collection 16.2.9 ShapeRange 16.2.10 Windows and ViewType 16.3 USING AN ALREADY OPEN PRESENTATION FILE 16.4 CREATING THE BASE FILE 16.5 UPDATING THE BASE FILE WITH MACROS 16.5.1 Transfer text to a specific location 16.5.2 Transfer data from an Excel Range 16.5.3 Transfer a chart 16.5.4 Transfer a table with varying number of rows

CHAPTER 17 MICROSOFT QUERY & DATABASES

17.1 SHORT VISIT TO THE DATABASE WORLD. 17.2 WORKING WITH MS QUERY 17.2.1 Launch MS query 17.2.2 Single table – The Query Wizard 17.2.3 Multiple tables – True power of MS query 17.2.4 MS Query interface 17.2.5 Selecting Data 17.2.6 Joining multiple tables 17.2.7 Setting Criteria 17.2.8 Closing MS Query 17.3 CREATING SUMMARIES IN MS QUERY 17.3.1 An example in Excel 17.3.2 Summary of imported data – Performance of sales force 17.3.3 Applying criteria to summarized rows 17.4 WORKING WITH IMPORTED DATA 17.4.1 Refreshing the data manually 17.4.2 Auto-Refresh at fixed intervals 17.4.3 Edit an existing query 17.4.4 Associating a formula 17.5 QUERY DATA AND VBA 17.5.1 Setup for VBA 17.5.2 Refreshing the data with VBA 17.5.3 Trigger a macro on data refresh 17.6 CONNECTING TO MS ACCESS 17.7 CONNECTING TO MYSQL (ODBC + WOOCOMMERCE) 17.7.1 Prepping up our database connection 17.7.2 Setting up ODBC driver and Data Source 17.8 CORPORATE DATABASES Part III: Building a 3-Tier application in Excel

CHAPTER 18 SCENARIO AND USAGE OF THE 3-TIER APPLICATION

18.1 INTRODUCTION 18.2 SCENARIO FOR DATA CAPTURE 18.3 SETTING UP THE DATABASE. 18.3.1 Setting up named ranges 18.3.2 Cells for holding data entered in forms

CHAPTER 19 CREATING FORMS ON A WORKSHEET

19.1 SETTING UP THE WORKSHEET 19.2 ADDING CONTROLS 19.3 SETTING PROPERTIES, CAPTION AND NAME OF CONTROLS 19.4 LINK TO CELLS, FORMULAS & MACROS 19.5 CONTROLS & MACROS (GENERAL FACTS) 19.5.1 Linking controls to macros 19.5.2 Events 19.5.3 Common properties 19.6 LISTBOX CONTROL 19.7 COMBOBOX 19.8 CHECKBOX 19.9 OPTIONBUTTON 19.9.1 Proper way of putting option buttons 19.10 BUTTON 19.11 SCROLLBAR 19.11.1 Fractional/Negative values & reversing the direction 19.12 SPIN BUTTON 19.12.1 Fractional/Negative values & reversing the direction

CHAPTER 20 CREATING VISUAL BASIC USERFORM

20.1 SETTING UP THE USERFORM & ADDING CONTROLS 20.2 SETTING NAMES AND PROPERTIES 20.3 FOCUS, TAB INDEX & TAB STOP 20.4 LINK TO MACROS 20.5 USERFORM 20.6 COMMON PROPERTIES OF CONTROLS 20.7 EVENTS FOR CONTROLS 20.7.1 Setting an Event 20.7.2 Common Events 20.8 LISTBOX 20.9 COMBOBOX 20.10 CAVEAT ABOUT SETTING ROWSOURCE 20.11 CHECKBOX 20.12 OPTIONBUTTON 20.13 COMMANDBUTTON 20.14 SPINBUTTON 20.15 SCROLLBAR 20.16 TEXTBOX 20.17 LABEL

CHAPTER 21 MIDDLEWARE – THE BRIDGE

21.1 CONVERTING FORM DATA TO DATABASE VALUES 21.1.1 When using a form on a worksheet 21.1.2 When using a VB UserForm 21.2 FORM VALIDATION 21.3 THE SWITCHBOARD FORM 21.4 CREATING OUTPUTS 21.4.1 Invoice 21.4.2 Top-5 components by volume

Solutions with excel by harish gopalkrishnan pdf.