Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 476
  • Last Modified:

Excel - Show Drill Down in Separate sheet

See attached analysis of "sales of cars by country and continent".

Fairly simple stuff!

First sheet show "Sales By Country"
Second sheet is a dynamic summary of first sheet (i.e. second sheet shows sales by continent).

Third Sheet - This is the tricky bit!

Question: How do I get the third sheet to show a "drill down analysis" of the continent chosen in the "Sales By Continent" sheet.

E.g. if the user places the cursor in cell "A4" of sales by continent THEN the "Drill Down" sheet should show all the countries in this continent.
ExcelEEDrilldown.xlsx
0
Patrick O'Dea
Asked:
Patrick O'Dea
  • 4
  • 3
1 Solution
 
Saurabh Singh TeotiaCommented:
Have you looked into option of making pivot tables in it.. You can do what you are looking for..

Enclosed is the pivot table for your reference which does what you are looking for..

In additional if you are using excel 2010.. You can further make slicers in it which will further simplify what you are trying to do..

Saurabh...
ExcelEEDrilldown.xlsx
0
 
Patrick O'DeaAuthor Commented:
Thanks Saurabh,

I am familiar with pivots.

The KEY to my requirement is that the drill down MUST be controlled by whatever cell the user is in - i.e. if user chooses  cell A2 in Sales By Continent then Europe's sales are shown in the pivot.

I wonder is it possible to automatically change the pivot filter via VBA.
So perhaps the pivot solution could work (but only if it was controlled the location of the cursor in the "Sales By Continent" sheet.
0
 
Saurabh Singh TeotiaCommented:
Same is controlled by pivot filter only..if you change your continent in B3 it will do what you are looking for..You can delete the rows on top and it can do what you are looking for..

Or perhaps you can help me further what you are looking for..I can write  a macro for you if above doesnt helps...
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Patrick O'DeaAuthor Commented:
test
ignore this
ECB-T4A-FrontEnd.mdb
0
 
Patrick O'DeaAuthor Commented:
back

test ignore
ECB-T4A-BackEnd.mdb
0
 
Saurabh Singh TeotiaCommented:
It's an access file..? You want to do this in access of excel?
0
 
Patrick O'DeaAuthor Commented:
Okay, thanks I have something working now.

The Pivot was a good solution
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now