Completed
min) Array Formula to extract Customer Name and Invoice Amount using INDEX, AGGREGATE, IF, ROW, COUNTIFS and ROWS functions.
Class Central Classrooms beta
YouTube videos curated by Class Central.
Classroom Contents
Aging Accounts Receivable Reports on Multiple Sheets With Array Formula
Automatically move to the next video in the Classroom when playback concludes
- 1 min) Overview of end result: Automatic Aging Reports
- 2 min) Convert Source Data Table (in separate workbook file) to an Excel Table and name the Table.
- 3 min) Import Source Data into Report File
- 4 min) Add Helper Column to Destination Excel Table in the Report Workbook file. First look at Table Formula Nomenclature (Structured References). The correct formula for Days Late is: =TODAY()-[@[Due…
- 5 min) COUNTIFS formula to count records that are between 1 and 30 days past due. Including syntax for greater than or equal to criteria when the comparative operator and a cell must be joined.
- 6 min) Array Formula to extract the "Number of Days Late" information for each customer, given two criteria. See how to use LARGE function and IF function and the array formula key stroke: Ctrl + Shif…
- 7 min) See how to use IF function rather than IFERROR function to create efficient Data Extraction Array Formulas.
- 8 min) Array Formula to extract the "Number of Days Late" information for each customer, given two criteria. See how to use the AGGREGATE function rather than the LARGE function to gain the benefits: …
- 9 min) Array Formula to extract Customer Name and Invoice Amount using INDEX, AGGREGATE, IF, ROW, COUNTIFS and ROWS functions.
- 10 min) Learn how to make Table Formula Nomenclature (Structured References) Absolute.
- 11 min) Copy Array Formula Reprt to Multiple Sheets.
- 12 min ) Test to see if data changed in the Source and Destination workbooks will be reflected in the Aging Reports automatically.
