Excel Magic Trick 1249

DESCRIPTION

Build Database with Excel 2016 Table feature & VLOOKUP to Get Invoice Detail
**Note; The word “Database” is used loosely in this video. We use it just to mean we have a dynamic table to store raw data.
Overview of video:
1) Build Invoice Database with Excel 2016 Table feature
2) Learn how Excel Table feature automatically carries formatting and formulas to new records
3) Learn how to use Table Formula Nomenclature / Structured References for Excel Table formulas
4) Learn about Dynamic Ranges with Excel Table feature
5) VLOOKUP and MATCH functions to lookup Invoice Details
Full Detailed list of what is in video including time hyperlinks:
(00:11) Look at finished database and VLOOKUP formula to understand what the goal of the video is.
(02:00) Database must be a Proper Data Set: 1) Field Names in First Row, 2) Records in subsequent Rows, 3) Empty Cells or Excel Column/Row Headers Around Data Set
(03:07) Add Bold Format to Field Names / Column Headers so database interprets headers correctly
(04:42) Custom Date Format to Date Field. This is carried forward to new records in Excel Table Database.
(05:58) Add word wrap to Product Description. This is carried forward to new records in Excel Table Database.
(06:05) Add Number Formatting: Currency and Percentage to number fields. This is carried forward to new records in Excel Table Database.
(06:58) Add formula for Amount owed after discount, tax rate and shipping. See the ROUND function. This is carried forward to new records in Excel Table Database.
(10:05) Convert Proper Data Set to Excel Table with Ctrl + T. Excel Table feature is Excel’s Database feature.
(11:32) Add new column to database for Balance Due. Learn about Table Formula Nomenclature / Structured References for Excel Table formulas.
(11:53) Name the Table.
(14:30) Add new records to test database.
(16:39) Add Proper Field names using Table Formula Nomenclature / Structured References for Excel Table formulas in the lookup area. Learn about relative references in Table Formula Nomenclature / Structured References for Excel Table formulas.
(18:50) Add Data Validation Drop Down List for invoice lookup.
(19:44) Create VLOOKUP and MATCH Lookup formula to lookup Invoice Detail. Learn about how to lock (absolute) references in Table Formula Nomenclature / Structured References for Excel Table formulas.
(24:46) Test database and lookup formulas.
(25:21) Summary and Conclusion

WORKBOOKS