Download File:

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:

1. (00:17) Introduction. Look at Dynamic End Result Solution.

2. (01:28) Table Formula Nomenclature (Structured References) and how they ract when they are copied.

3. (02:35) Formula to Extract Sheet Name. See the functions: CELL, REPLACE and SEARCH.

4. (04:42) Formula to Count the number of records in the Master Table for each product. See the function COUNTIFS.

5. (05:57) Array formula to extract records based on Criteria. See the functions: IF, ROWS, INDEX, AGGREGATE, ROW and a number of Array Operations.

6. (17:25) Add Formula to calculate total revenue.

7. (19:07) How to Quickly Copy Worksheets using a Keyboard and Mouse.

8. (20:25) Test Where Master Table and Sub Tables are Dynamic and will update when new records are added.

9. (21:06) Summary & Considerations for Advantages and Disadvantages to using Array Formulas.

Extract Product Records From Master Table to Multiple Sheets (Sub Tables). How to Retrieve Multiple Records from One Lookup Value. Return Multiple Records from One Lookup Value.

Other Similar Videos:

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

Excel Magic Trick 1347: Power Query: Split Master Table into Sub Tables, Each Product on New Sheet

Nguồn: https://12m-15m.org/

Xem thêm bài viết khác: https://12m-15m.org/category/cong-nghe

hello sir i typed the array formula but i am getting #Num! error whats my fault, please give solution..

i have an error in countifs, when i type countifs(fs no icon of table comes there and i cant select the table header… please solve this

very excellent video… i am just searching for this ……. thanks bro.

thank you very much thats great its very usefull and helpfull

well done

This is one of the most ingenious and clever ways to use Excel, kudos!

1:42

Hi Mike

Thanks for an exceptionally useful video. You are great as usual. However, when I am practising in the downloaded exercise file, the formula is working fine for the first column but it is not automatically changing according to next column header. Why this is So?

The download link unavailable. can you send the file to my mail please? pb.olali@gmail.com thanks

I trying this way but it doesn't show . I don't know why .

Exactly at 10:35 can I use a function similar to LIKE so I can search for values inside fields that might contain other characters as well? So instead of =$B$1 can we do something like LIKE $B$1?

I don't what to tell you man. PURE GENIUS!

Absolutely awesome. No idea how anyone could know this much about excel, but I am very grateful that you are willing to share with so many of us. God bless!

Is there a master table row limit?

sir good job.. but i have one question. how about if you have a two criteria?? pls help me sir

This is terrible, I wanted to learn ONE thing! The video taught me dozens of things! That's just reckless teaching! Now I'm going to be the one in the office everyone comes to for excel help! Joking aside, this is a great teaching style. I love how you use the F9 feature to show the formula returns every-time I think I'm lost.

This is routine to mention that you are fantastic

Your brain is scary 😊 in a great way!

In the video the criteria is the Product. What if a second criteria is required? The date to be exact. And I just want to pull data referring to "this product" only on "this month"? how would the formula go?

my head hurts trying to figure it out myself

update! figured it out!! injust need to eat. hahaha

Hi Mike, thanks for your video, Its better if we change the ROWS(A$5:A5… become ROW(A1) for both?

Hey there is one limitation, it cant be sorted like if A to Z or Z to A….. ?

Beautiful and nice effort. I think it can be done with pivot table m i right?

Many thanks for sharing….

How would I transfer blanks from master to sub tables in this formula? I am getting a date return of 1/0/1900

How can i have sub table with certain columns without any criteria.

Basically exact copy of master table with certain columns.

Of course, we gonna like your video

mind blowing

could you please assist us in developing in knowlege for macro by uploading the vedios

Hey Mike. I was playing with the file and added a second criteria and this is what I arrived at with category as second criterion:

=IF(ROWS(A$5:A5)>$B$2,"",INDEX(fSales[Date],AGGREGATE(15,6,(ROW(fSales[Date])-ROW(fSales[#Headers])/((fSales[[Product]:[Product]]=$B$1)*(fSales[[Category]:[Category]]=$E$1))),ROWS(A$5:A5))))

Is there another way without multiplying the conditions?

You and Sal Khan are pioneers and your work will live long after you.