Managing Data with Microsoft Excel
- Managing Data with Microsoft Excel
- Table of Contents
- Copyright
- About the Author
- Acknowledgments
- We Want to Hear from You
- Introduction
- About Excel
- How to Use This Book
- Part I: Pitfalls in Data Management
- Chapter 1. Misusing Excel as a Database Management Tool
- Putting Data Management into Context
- CASE STUDY Overworking Functions
- Transposing Rows and Columns
- Using Labels Instead of Codes
- Handling Variable Numbers of Records per Category
- Changing Horses
- Part II: Managing Data Inside Excel
- Chapter 2. Excel s Data Management Features
- Using the Worksheet Functions: An Overview
- Locating Data with OFFSET
- Finding Data with INDEX
- Finding Data with MATCH
- Using MATCH with Other Functions
- Using VLOOKUP
- Rearranging Data with TRANSPOSE
- Getting Data from a Pivot Table: GETPIVOTDATA
- Looking Ahead
- Chapter 3. Excel s Lists, Names, and Filters
- Creating Lists
- Sorting Lists
- Working with Names
- Filtering Data with the AutoFilter
- Using the Advanced Filter
- Looking Ahead
- Part III: Managing External Data from Inside Excel
- Chapter 4. Importing Data: An Overview
- Getting External Data into the Workbook
- CASE STUDY
- Importing Data to Pivot Tables
- Looking Ahead
- Chapter 5. Using Microsoft Query
- Understanding
- Querying Multiple Tables
- CASE STUDY
- Joining Parent and Child Records
- Using Database Queries
- Handling the Data Range
- Managing Boolean Fields and Check Boxes
- Looking Ahead
- Chapter 6. Importing Data: Further Considerations
- Understanding Pivot Tables
- Preparing Data for Pivot Tables
- Using Criteria with Microsoft Query
- Querying Web Sites
- Looking Ahead
- Part IV: Managing Databases from Inside Excel
- Chapter 7. VBA Essentials Reviewed
- Using VBA to Manage Data
- Establishing Subroutines
- Understanding Dot Notation
- Using Loops
- CASE STUDY
- Using With Blocks
- Understanding the Macro Recorder s Code
- Using Object Variables
- Looking Ahead
- Chapter 8. Opening Databases
- Connecting to a Database
- Connecting Using ADO
- Specifying the Data Source
- Opening the Connection
- Opening a Database Using DAO
- Dealing with Secured Jet Databases
- Opening a Secured Access Database
- Looking Ahead
- Chapter 9. Managing Database Objects
- Creating Tables
- Defining Fields
- Creating Queries
- CASE STUDY
- CASE STUDY
- Looking Ahead
- Chapter 10. Defining Fields and Records with ActiveX Data Objects and Data Access Objects
- Creating Databases from Excel
- Creating a New Database Using DAO
- Creating a Table and Fields Using ADO
- CASE STUDY
- Declaring and Using Recordsets
- Understanding DAO Recordset Types
- Understanding ADO Recordset Types
- Looking Ahead
- Chapter 11. Getting Data from Access and into Excel with ADO and DAO
- Using CopyFromRecordset
- Building an Application
- Bringing Data Back from a Parameterized Query
- Returning Data from a Database to a User Form
- Looking Ahead
- Chapter 12. Controlling a Database from Excel Using ADO and DAO
- Using DAO to Execute Action Queries
- CASE STUDY
- Using ADO to Execute Action Queries and Stored Procedures
- CASE STUDY
- Using DAO Recordsets to Move Data from Excel to a Jet Database
- Using ADO to Move Data from Excel to a Database
- Looking Ahead
- Index
- index_SYMBOL
- index_A
- index_B
- index_C
- index_D
- index_E
- index_F
- index_G
- index_H
- index_I
- index_J
- index_K
- index_L
- index_M
- index_N
- index_O
- index_P
- index_Q
- index_R
- index_S
- index_T
- index_U
- index_V
- index_W
- index_X