Excel Magic Trick 1346

Array Formula: Split Master Table into Sub Tables, Each Product on New Sheet


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:
(00:17) Introduction. Look at Dynamic End Result Solution.
(01:28) Table Formula Nomenclature (Structured References) and how they react when they are copied.
(02:35) Formula to Extract Sheet Name. See the functions: CELL, REPLACE and SEARCH.
(04:42) Formula to Count the number of records in the Master Table for each product. See the function COUNTIFS.
(05:57) Array formula to extract records based on Criteria. See the functions: IF, ROWS, INDEX, AGGREGATE, ROW and a number of Array Operations.
(17:25) Add Formula to calculate total revenue.
(19:07) How to Quickly Copy Worksheets using a Keyboard and Mouse.
(20:25) Test Where Master Table and Sub Tables are Dynamic and will update when new records are added.
(21:06) Summary & Considerations for Advantages and Disadvantages to using Array Formulas.


Download “Excel-Magic-Trick-1341-1360.zip” Excel-Magic-Trick-1341-1360.zip – Downloaded 211 times – 23 MB

Be the first to comment

Leave a Reply