Pivot table? List? Advanced

M

mickeystanford_alumni

Board Regular
Joined
May 11, 2022
Messages
129
Office Version
  1. 2021
Platform
  1. Windows
  2. MacOS
Hi all,
I am trying to build the following:
Two sheets, one with the below format, and another which is a database which brings the information from SQL through a query.
In the first sheet (my main sheet) I am trying to create a dynamic visualisation tool.
Basically in my DB I have origin countries, destination countries, type of sales (tech, miscellaneous, etc), amount of those sales, month and year.
In the main sheet, I want to select the country of origin and the type of sale (I thought on a drop-down list), and by doing so the excel populates me the countries of destination with the respective month and year amounts.

The tricky thing here is that for simplicity I put two destination countries, however, the DB has basically all countries in the world. Some such as US sell to China, but others such as Chile do not sell to China. Therefore, in my main sheet, when I select the country of origin, I would like to have populated only the countries where the ORIGIN sells to (e.g. US sells to 25 counties as per my DB, but Chile only to 4, therefore I would expect 25 small tables with destination originated for US but only 4 for Chile).

Let me know if there is any brilliant idea you might come with.
I thought on a drop down list, but haven't been successful at all.
If not, a Pivot Table might be better, but I am not an expert with PT's whatsoever, but I guess there might be a nice solution with any add-in/code that can populate this.
The goal is that when selecting a country and a sale type, the sheets populates quickly the information. It is at the end a visualisation tool to track sales.
I post below XL2BB with main sheet and DB.

Thxs so much.

Book3
ABCDEFGHIJKLMNO
1COUNTRYUSASALESTECH
2
3
4
5SALESYEARTOTAL123456789101112
6TECH2021600505050505050505050505050
7TECH2022720606060606060606060606060
8TECH20231200100100100100100100100100100100100100
9
10DESTINATION COUNTRYYEARTOTAL123456789101112
11CHINA2021300252525252525252525252525
12CHINA2022360303030303030303030303030
13CHINA2023600505050505050505050505050
14
15DESTINATION COUNTRYYEARTOTAL123456789101112
16EUROPE2021300252525252525252525252525
17EUROPE2022360303030303030303030303030
18EUROPE2023600505050505050505050505050
MAIN




Book3
ABCDEF
1ORIGIN COUNTRYDESTINATION COUNTRYSALESAMOUNTYEARMONTH
2USACHINATECH2520211
3USACHINATECH2520212
4USACHINATECH2520213
5USACHINATECH2520214
6USACHINATECH2520215
7USACHINATECH2520216
8USACHINATECH2520217
9USACHINATECH2520218
10USACHINATECH2520219
11USACHINATECH25202110
12USACHINATECH25202111
13USACHINATECH25202112
14USACHINATECH3020221
15USACHINATECH3020222
16USACHINATECH3020223
17USACHINATECH3020224
18USACHINATECH3020225
19USACHINATECH3020226
20USACHINATECH3020227
21USACHINATECH3020228
22USACHINATECH3020229
23USACHINATECH30202210
24USACHINATECH30202211
25USACHINATECH30202212
26USACHINATECH5020231
27USACHINATECH5020232
28USACHINATECH5020233
29USACHINATECH5020234
30USACHINATECH5020235
31USACHINATECH5020236
32USACHINATECH5020237
33USACHINATECH5020238
34USACHINATECH5020239
35USACHINATECH50202310
36USACHINATECH50202311
37USACHINATECH50202312
38USAEUROPETECH2520211
39USAEUROPETECH2520212
40USAEUROPETECH2520213
41USAEUROPETECH2520214
42USAEUROPETECH2520215
43USAEUROPETECH2520216
44USAEUROPETECH2520217
45USAEUROPETECH2520218
46USAEUROPETECH2520219
47USAEUROPETECH25202110
48USAEUROPETECH25202111
49USAEUROPETECH25202112
50USAEUROPETECH3020221
51USAEUROPETECH3020222
52USAEUROPETECH3020223
53USAEUROPETECH3020224
54USAEUROPETECH3020225
55USAEUROPETECH3020226
56USAEUROPETECH3020227
57USAEUROPETECH3020228
58USAEUROPETECH3020229
59USAEUROPETECH30202210
60USAEUROPETECH30202211
61USAEUROPETECH30202212
62USAEUROPETECH5020231
63USAEUROPETECH5020232
64USAEUROPETECH5020233
65USAEUROPETECH5020234
66USAEUROPETECH5020235
67USAEUROPETECH5020236
68USAEUROPETECH5020237
69USAEUROPETECH5020238
70USAEUROPETECH5020239
71USAEUROPETECH50202310
72USAEUROPETECH50202311
73USAEUROPETECH50202312
DATA
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
With Pivot table, follow these steps:

First, the "MAIN" sheet must be clean.

Step 1.
Select the data in the "DATA" sheet.​

Step 2.
From the Menu select Insert, Pivot table.​

Step 3.
Select Existing worksheet from Create pivottable window, then select the "MAIN" sheet, select cell C4 and press Ok.​

Step 4.
From the right side panel we are going to organize the pivot table.
Then select the ORIGIN COUNTRY field and drag it into the filter area.​
Same with the SALES field.​
​
Drag the fields​
DESTINATION COUNTRY and YEAR within the Row area.​
​
Drag the field​
MONTH within the Column area.​
​
Drag the AMOUNT field into the Value area.​
1700874842127.png


Step 5.
Right click at any cell in the pivot table to show the context menu, and select PivotTable Options:​
1700875103959.png

Step 6.
In the PivotTable Options dialog, click Display tab and check Classic PivotTable Layout (enables dragging fields in the grid) option.​

doc-classic-pivottable-layout-2

Step 7.
Click OK to close the dialog, and now the pivot table layout change.​


Step 8.
On PivotTable Tools, select Design option, select Report Layout, select Repeat All Item Labels:​
1700875443462.png

Step 9.
To filter by country or by sale, select the arrow at the top of the table.​
1700875737548.png

I hope it helps you.

Regards
Dante Amor
:giggle:
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
Finding Distinct Values with a Formula
Replies
5
Views
494
Fluff
Fluff
G
  • Question
Consolidating 2 columns based on value in another column to another Worksheet
Replies
5
Views
363
Peter_SSs
Peter_SSs
J
  • Solved
Auto-populate cell if data validation list has only one option
Replies
7
Views
240
Jeffrey Mahoney
Jeffrey Mahoney
F
  • Question
Spill in a table
Replies
2
Views
818
Fudging
F
C
  • Question
populate unit price dependent on area and codes selected.
Replies
3
Views
135
awoohaw
awoohaw

Forum statistics

Threads
1,215,069
Messages
6,122,959
Members
449,096
Latest member
Anshu121

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top