Excel Magic Trick 538: Dynamic Sub Tables Based On Master Sheet Array Formula & Drilling Through

[marysunnymccoy]

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?

[WestMauiRFL]

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

[ExcelIsFun]

Send workbook with explanation of what problem is to: excelisfun at gmail.

[ExcelIsFun]

#NUM! usually means that the count formula counted to many.

Otherwise, I am not sure what the problem is without looking at the workbook.

[mv2junk]

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!

[ExcelIsFun]

send file to: excelisfun at gmail with question stated clearly.

[chinchonchinchon]

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.

[ExcelIsFun]

This is a hard formula, so if you understand it even a little bit, you are doing quite well in Excel!

[hobb1314]

One word... amazing!!!

More power... Keep it coming...

[ExcelIsFun]

You are welcome!

[mikeyroro]

Mike, this formula was so slick, my fingers kept slipping off my keyboard! Love this stuff!

[ExcelIsFun]

I am glad that you love this stuff! I love making the videos!

[hamy72]

That is sheer Magic!!! Mike is it possible to pull a rabbit out using an Array Formula??.......lol.

Thanks! waiting for the next video!

[ExcelIsFun]

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!

[drpokerbr]

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...

[ExcelIsFun]

What do you mean by "minor formula"?

[drpokerbr]

sorry, small formula ( in portugues is minor...)

[ExcelIsFun]

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 ...

[kennis942]

woo 7 views ..first comment :D

Nice vid! *rate*

[ExcelIsFun]

Thanks! This was a fun vid to make because we got to do array formula and DrillDown!