Drill Down With Out Pivot

[Nabarun Debnath]

How could we Drill Down data with out creating any pivot table, to show details of any summary content…

If any VBA code can do so, plz help…

 

[NARAYANK991]

Hi ,

It is good that you have uploaded your workbook , but in the absence of a detailed explanation of what you want done , there is very little that anyone can offer by way of help.

A pivot table summarizes data , and drilling down exposes the underlying data behind a summary figure.

In your workbook , which are these summary figures , and how would the result of a drill down be displayed ?

If you can present the layout of at least one such drill down , it may be easier for others to understand how your requirement can be fulfilled.

However , my personal opinion is that this is at best an academic exercise , since it will involve a lot of coding ; given that a pivot table does the same effortlessly , both on the part of the software and on the part of the user , I do not see why this exercise is being attempted.

Narayan

 

[Nabarun Debnath]

Sir,
My managers don't appreciate large size work books, and if you see my workbook, it has separate summary sheets like "1~n Coll", "UV+8K", "3RF" & so on...
All these summary sheets can be made by simple pivot but file size may extend...
So i need a lil help on this...

And to understand my query i would say, suppose if i create "1~n Coll" sheet by pivot with source data of "Data sheet" and double click h4 cell of "1~n Coll" sheet it should open a new worksheet with underlying data for which the 9 lac collection happened... But as i made this file with formula it won't happen...
So here i need the help of macro...

I'am attaching my master file for everyone's understanding the formula...

 

[NARAYANK991]

Hi ,

If , as you say , the H4 cell contains a formula which returns a summary figure , how can we drill down without knowing the formula ?

Assuming we know the formula , how can we parse the formula which may use the SUMPRODUCT function and have multiple components involving arrays , conditions ?

A generic drill down is practically impossible.

You need to discuss this with your manager(s).

Narayan

 

[David Evans]

You need to convince your managers that Pivot Tables are good things, not a manifestation of the devil!

I'm bemused at how often we see requests for solutions/help that specifically exclude the use of Pivot Tables - what is it about them that scares folks?

 

[Chihiro]

All these summary sheets can be made by simple pivot but file size may extend...

This is true when you create each pivot table from data source. Since this forces Excel to replicate identical PivotCache for each pivot table created.

Instead, create single pivot table from the data source. Then copy and paste entire pivot table to desired locations (sheets). Then adjust the fields for each of copied pivot table.

When pivot tables are created in this manner, all pivot tables will share same PivotCache and does not inflate file size (it will only increase by small amount needed to generate pivot table, and not the underlying data cache).

 

[NARAYANK991]

Hi ,

More details are available here :

http://datapigtechnologies.com/blog/index.php/cut-the-size-of-your-pivot-table-workbooks-in-half/

Narayan

 

[Nabarun Debnath]

Thanks a lot both of u!!!