Single Cell Spilled Reports with Dynamic Total Row & Formatting. Excel Magic Trick 1703.

Download Excel File: https://excelisfun.net/files/EMT1703.xlsx
Learn how to use the LET function to create single cell reports. Learn basics to advanced LET function tips, including dynamic total row and formatting.
Topics:
1. (00:00) Introduction
2. (00:15) Look at other videos in series.
3. (00:26) Look at Dynamic Total Row in report!
4. (00:57) How LET function can be efficient.
5. (01:24) Arguments in LET function.
6. (01:43) Alt + Enter to add new lines for variables in the LET function.
7. (01:50) First three variables.
8. (02:10) Variables for minimum date and start date.
9. (02:37) Philosophy for how to build LET variables and view result for each variable before using them in other places in formula.
10. (04:10) Variables for month unique list and month count.
11. (04:48) Variables for unique list of persons and person count.
12. (05:22) Variables for number of records, rows, cells. Variable sequence starting at zero.
13. (07:03) Formula for repeating sequence to lookup up people’s names using INT function to get 1,1,1,2,2,2… pattern.
14. (08:43) Variable for people names in report using INDEX function.
15. (09:29) Variable for repeating sequence to create start and end dates using MOD function to get 0,1,2,0,1,2… pattern.
16. (11:11) Variable for Start Dates using EDATE.
17. (11:59) Variable for End Dates using EOMONTH.
18. (12:32) Variable for total sales with conditions using SUMIFS function.
19. (13:45) Variable for pulling three columns together for final report using CHOOSE function.
20. (14:45) Add Number Formatting.
21. (14:59) Add Total Rows using SWITCH function. This is the final calculation in LET that delivers final Report.
22. (17:53) Edit variable for month count to accommodate dtat that spans multiple years.
23. (20:39) Add new data and test single cell formula using LET.
24. (20:50) Conditional Formatting to format dynamic report, including Grand Total Cell.
25. (22:38) Add New Variable to help Filter out Zeros. (Bonus Trick)
26. (23:57) Summary
27. (24:28) Closing and Video Links

Other videos in this series:
Basics of SUMIFS & PivotTables for Monthly Sales Report. Excel Magic Trick 1701
Excel Spilled Array Formulas to create Monthly Sales Report. Excel Magic Trick 1702
LET Function to create Monthly Sales Report in Single Cell with Total Row. Excel Magic Trick 1703

Sequential Numbers 1,1,1,2,2,2. Incrementing Numbers. Formula Number Incrementor.
Sequential Numbers 1,2,3,1,2,3. Incrementing Numbers. Formula Number Incrementor.

Видео Single Cell Spilled Reports with Dynamic Total Row & Formatting. Excel Magic Trick 1703. канала ExcelIsFun