Excel Magic Trick 538: Dynamic Sub Tables Based On Master Sheet Array Formula & Drilling Through
Loading...
Link to this comment:
Uploader Comments (ExcelIsFun)
All Comments (21)
-
This is really great! thanks!
When I fill this in, I am getting #Value errors on the first row and #NUM errors on the lower rows, but if I hit F9 the correct value shows up. Any clue what is going wrong?
-
#NUM! usually means that the count formula counted to many.
Otherwise, I am not sure what the problem is without looking at the workbook.
-
You are welcome!
-
One word... amazing!!!
More power... Keep it coming...
-
I am glad that you love this stuff! I love making the videos!
-
Mike, this formula was so slick, my fingers kept slipping off my keyboard! Love this stuff!
-
These three videso explain this type of formulas (inlcuding SMALL) much better:
Excel Magic Trick 358: Part 1: Return Multiple Items From One Lookup Value ...
Excel Magic Trick 360: Part 3: Return Multiple Items From One Lookup Value ...
Excel Magic Trick 359: Part 2: Return Multiple Items From One Lookup Value ...
-
sorry, small formula ( in portugues is minor...)
-
I'll try and create an array formula to take a rabbit from a hat! If I can't figure it out, I'll ask someone at the Mr Excel Message Board!
-
What do you mean by "minor formula"?
-
That is sheer Magic!!! Mike is it possible to pull a rabbit out using an Array Formula??.......lol.
Thanks! waiting for the next video!
-
one more great vid...
thank u
I would like to understand the minor function in this formula, do u have a vid related with the minor formula?
Keep going...
-
Thanks! This was a fun vid to make because we got to do array formula and DrillDown!
-
woo 7 views ..first comment :D
Nice vid! *rate*
4:16
Excel Magic Trick 537: Drill Through Spreadsheets -- Formatting, Formulas, Editingby ExcelIsFun2,511 views- 7:11
Excel Magic Trick 548: Data Validation Drop-Down List In A Cell Same Sheet or Different Sheetby ExcelIsFun34,115 views
- 5:50
Excel Magic Trick 539: Extract New Records Not In Old List -- Filter Methodby ExcelIsFun4,242 views
- 10:58
Excel Magic Trick 549: Dynamic Data Validation List Drop-Down OFFSET or Table Feature?by ExcelIsFun23,798 views
- 9:20
Excel Magic Trick #213: Dynamic Data Extract w Formulaby ExcelIsFun7,813 views
- 4:02
Using Excel as a Databaseby HugoPhongPresents4,905 views
- 8:05
Excel Magic Trick 534: Conditional Formatting for Weekends and Holidaysby ExcelIsFun24,687 views
- 7:34
Excel Magic Trick 606: INDEX & MATCH Two Lookup Valuesby ExcelIsFun16,269 views
- 7:57
Excel Magic Trick 536: List All Modes in Multimodal Data Set MODE.MULT Excel 2010 Functionby ExcelIsFun3,853 views
- 7:58
Excel Lookup Series #1: VLOOKUP Function 1st Exampleby ExcelIsFun317,024 views
- 5:53
factura excel plantilla con base de datos incluida mas rapido mas versatil mas potente jfksoft.comby jfksoft65,409 views
- 7:01
Excel: Drop-Downs(Combo Box) and Conditional Formatting (Voice Tutorial)by iProRyan36,705 views
- 6:39
Excel Magic Trick 550: Data Validation List Drop-Down Based On 2nd Data Validation List Drop-Downby ExcelIsFun23,297 views
- 8:18
Excel Magic Trick #146 part 2: Dynamic List from Table #1by ExcelIsFun9,633 views
- 9:44
Excel Magic Trick 557: CHOOSE function to lookup Formula or Function (2 Examples).by ExcelIsFun7,671 views
- 9:46
Excel Magic Trick #90: Consolidationby ExcelIsFun47,856 views
- 8:01
Excel Magic Trick 650: Formula To Extract All Rows In Data Set Into 1 Column INDEX functionby ExcelIsFun3,532 views
- 5:57
Excel Magic Trick 535: Count Weekdays Including Half Days NETWORKDAYS & NETWORKDAYS.INTLby ExcelIsFun3,242 views
- 4:02
Excel Magic Trick 581: Unique List With Criteria Using Advanced Filterby ExcelIsFun4,729 views
- 6:28
How to Merge Multiple Excel Workbooks to a Master Budgetby DannyRocksExcels41,052 views
Wow! Thanks for the excellent video, it is really helping me see the light of day on one of my spreadsheets. Seeing formulas like this renew my spirits always keep learning in excel.
I'm having the same problem as mv2junk. My workbook will not show the added rows on the table. I don't know what to do but I'm hoping is a simple problem because when I downloaded your example it wouldn't update either. I'm using Excel 2007.
Any pointers would be greatly appreciated. Mahalo
WestMauiRFL 1 year ago
Send workbook with explanation of what problem is to: excelisfun at gmail.
ExcelIsFun 1 year ago
Excellent Vid!! for bookmark and easy to follow the Excel Guru!..
I tried an extension of this as an example - new file from scratch, and not sure why my 'Count' wouldn't increment dynamically as new row gets added in the MS sheet. I can see the values being referenced properly, however, the count wouldn't increment and subsequently, I do not get to see the new data in the tab sheets, because the 'if' condition > count returns true with a "" blank row. Any help will be appreciated!
mv2junk 1 year ago
send file to: excelisfun at gmail with question stated clearly.
ExcelIsFun 1 year ago
The first time I saw this formula, as I mentioned to you the other day Mike, I was overwhelmed. Now I understand it perfectly.
My first thoughts were: Why in the world is Mike using the Small formula, what?
I consider Excel, besides being such an excellent! tool for office and work a good with which way to develop our logical thinking abilities.
Wonderful lesson.
chinchonchinchon 1 year ago
This is a hard formula, so if you understand it even a little bit, you are doing quite well in Excel!
ExcelIsFun 1 year ago