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
Patrick O'DeaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Saurabh Singh TeotiaConnect With a Mentor Commented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.