See how to use formulas to extract records from a Master Table to Sub Tables on Each Excel Worksheets for each product in the Master Table:
1. (00:17) Introduction. Look at Dynamic End Result Solution.
2. (01:28) Table Formula Nomenclature (Structured References) and how they ract when they are copied.
3. (02:35) Formula to Extract Sheet Name. See the functions: CELL, REPLACE and SEARCH.
4. (04:42) Formula to Count the number of records in the Master Table for each product. See the function COUNTIFS.
5. (05:57) Array formula to extract records based on Criteria. See the functions: IF, ROWS, INDEX, AGGREGATE, ROW and a number of Array Operations.
6. (17:25) Add Formula to calculate total revenue.
7. (19:07) How to Quickly Copy Worksheets using a Keyboard and Mouse.
8. (20:25) Test Where Master Table and Sub Tables are Dynamic and will update when new records are added.
9. (21:06) Summary & Considerations for Advantages and Disadvantages to using Array Formulas.
Extract Product Records From Master Table to Multiple Sheets (Sub Tables). How to Retrieve Multiple Records from One Lookup Value. Return Multiple Records from One Lookup Value.
Other Similar Videos:
Excel Magic Trick 538: Dynamic Sub Tables Based On Master Sheet Array Formula & Drilling Through
Excel Magic Trick 1347: Power Query: Split Master Table into Sub Tables, Each Product on New Sheet
Xem thêm bài viết khác: https://12m-15m.org/category/cong-nghe