Excel Magic Trick 1249: Build Database with Excel 2016 Table feature & VLOOKUP to Get Invoice Detail
Download File: https://people.highline.edu/mgirvin/excelisfun
.htm
**
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:
1) (00:11)
Look at finished database and VLOOKUP formula to understand what the goal of the video is.
2) (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
3) (03:07) Add
Bold Format to
Field Names / Column Headers so database interprets headers correctly
4) (04:42)
Custom Date Format to Date Field. This is carried forward to new records in Excel Table Database.
5) (05:58) Add word wrap to
Product Description. This is carried forward to new records in Excel Table Database.
6) (06:05) Add
Number Formatting:
Currency and Percentage to number fields. This is carried forward to new records in Excel Table Database.
7) (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.
8) (10:05)
Convert Proper Data Set to Excel Table with
Ctrl + T. Excel Table feature is Excel’s Database feature.
9) (11:32) Add new column to database for
Balance Due. Learn about Table Formula Nomenclature / Structured References for Excel Table formulas.
10) (11:53)
Name the Table.
11) (14:30) Add new records to test database.
12) (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.
13) (18:50) Add Data
Validation Drop Down List for invoice lookup.
14) (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.
15) (24:46)
Test database and lookup formulas.
16) (25:21) Summary and
Conclusion
For more videos about The Excel Table feature and Table Formula Nomenclature (Structured References):
https://www.youtube.com/playlist?list=PLrRPvpgDmw0kmoAhXamTJw6XkebJ6gCxg