Drop Down-list? Or any other idea on how to build best this file?

M

mickeystanford_alumni

Board Regular
Joined
May 11, 2022
Messages
129
Office Version
  1. 2021
Platform
  1. Windows
  2. MacOS
Dear All,
Hope you're all in good health.
I am trying to make a file which contains the sales from the last 3 years coming from different origins and destinations. I want the file to be populated from a database that contains all information.
My struggle is on how to build this, so that it is easily automated.
Wondering if anyone could give me an idea on how to best make it.
The structure would be the following (apologies it's not an easy one).

As you can see, my plan is to select the continent from the dropdown list, and it will pop up the total merchandising sales (1 and 2) done TO Asia.
Below, I want to have the Merchandising ORIGINATION, so that if my total Sales do not make sense, I scroll down and I change one of the origins.
The problem here is, the Origins have a formula associated with them (coming from the database) so the issue here is:

1. Is there any way that when I select the continent from the dropdown list, the origins that appear (it's 4 different continents), show from biggest sales to lowest. So that if Europe Merchandising 1 is the biggest seller, I see the first table showing Europe. However, if in the dropdownlist I have America, and the biggest seller there is Asia, then Asia is in the first column.

2. What would be the best way to show these origins? As I have two options (as per my understanding). 1. to populate the data of those origins from other sheets (this will be messy). 2. to populate these origins from the database, however, the main Sheet I want to have it very simplified. Any idea on how to do it here?

Apologies because it might be hard to understand.
Appreciate your feedback.

ASIA - DROPDOWNLIST
TOTALJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
2021​
nananananananananananana
2022​
nananananananananananana
2023​
nananananananananananana
TOTAL MERCH.1JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
2021​
nananananananananananana
2022​
nananananananananananana
2023​
nananananananananananana
TOTAL MERCH. 2JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
2021​
nananananananananananana
2022​
nananananananananananana
2023​
nananananananananananana
ORIGINATION
MERCHANDISING1JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
2021​
nananananananananananana
2022​
nananananananananananana
2023​
nananananananananananana
MERCHANDISING1JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
2021​
nananananananananananana
2022​
nananananananananananana
2023​
nananananananananananana
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Hello,

I may not be on the right track for what you need, but I will try. I would reorganize the data so that I can use a continent dropdown cell A3 that will sort (by column C 'total') and filter the data. The 2 is the column indicator for sorting and the -1 is for largest to smallest order. I would use separate sheets as well but for mini excel in this example, I placed it all in one sheet. C10:H10 is the table where the data is, not the result which spills from cell B3 across.
=SORT(FILTER(B11:H40,A11:A40=A3),2,-1)

I hope this is helpful.

KED calc.xlsx
ABCDEFGHIJKLMN
1
2DateTotalTotal Merch1Total Merch2OriginationMerchandising 1Merchandising 2
3Asia8/31/2022186015Antartica624
49/30/2022155013Antartica520
510/31/2022124011Antartica416
611/30/20229309Antartica312
712/31/20226207Antartica28
87/31/20223105Antartica14
9
10ContinentDateTotalTotal Merch1Total Merch2OriginationMerchandising 1Merchandising 2
11Asia7/31/2022$3$10$5Antartica$1$4
12Asia12/31/2022$6$20$7Antartica$2$8
13Asia11/30/2022$9$30$9Antartica$3$12
14Asia10/31/2022$12$40$11Antartica$4$16
15Asia9/30/2022$15$50$13Antartica$5$20
16Asia8/31/2022$18$60$15Antartica$6$24
17US7/31/2022$21$70$17Brazil$7$28
18US6/30/2022$24$80$19Brazil$8$32
19US5/31/2022$27$90$21Brazil$9$36
20US4/30/2022$30$100$23Brazil$10$40
21US3/31/2022$33$110$25Brazil$11$44
22US2/28/2022$36$120$27Brazil$12$48
23US1/31/2022$39$130$29Brazil$13$52
24US12/31/2021$42$140$31Brazil$14$56
25UK1/31/2023$45$150$33Poland$15$60
26UK12/31/2022$48$160$35Poland$16$64
27UK11/30/2022$51$170$37Poland$17$68
28UK10/31/2022$54$180$39Poland$18$72
29UK9/30/2022$57$190$41Poland$19$76
30UK8/31/2022$60$200$43Poland$20$80
31UK7/31/2022$63$210$45Poland$21$84
32Germany6/30/2022$66$220$47Austria$22$88
33Germany5/31/2022$69$230$49Austria$23$92
34Germany4/30/2022$72$240$51Austria$24$96
35Germany3/31/2022$75$250$53Austria$25$100
36Germany2/28/2022$78$260$55Austria$26$104
37Germany1/31/2022$81$270$57Austria$27$108
38Germany12/31/2021$84$280$59Austria$28$112
39Germany11/30/2021$87$290$61Austria$29$116
40Germany10/31/2021$90$300$63Austria$30$120
Sheet2
Cell Formulas
RangeFormula
B3:H8B3=SORT(FILTER(B11:H40,A11:A40=A3),2,-1)
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
A3List=$O$2:$O$5
 
Upvote 0
Greenbehindthecells said:
Hello,

I may not be on the right track for what you need, but I will try. I would reorganize the data so that I can use a continent dropdown cell A3 that will sort (by column C 'total') and filter the data. The 2 is the column indicator for sorting and the -1 is for largest to smallest order. I would use separate sheets as well but for mini excel in this example, I placed it all in one sheet. C10:H10 is the table where the data is, not the result which spills from cell B3 across.
=SORT(FILTER(B11:H40,A11:A40=A3),2,-1)

I hope this is helpful.

KED calc.xlsx
ABCDEFGHIJKLMN
1
2DateTotalTotal Merch1Total Merch2OriginationMerchandising 1Merchandising 2
3Asia8/31/2022186015Antartica624
49/30/2022155013Antartica520
510/31/2022124011Antartica416
611/30/20229309Antartica312
712/31/20226207Antartica28
87/31/20223105Antartica14
9
10ContinentDateTotalTotal Merch1Total Merch2OriginationMerchandising 1Merchandising 2
11Asia7/31/2022$3$10$5Antartica$1$4
12Asia12/31/2022$6$20$7Antartica$2$8
13Asia11/30/2022$9$30$9Antartica$3$12
14Asia10/31/2022$12$40$11Antartica$4$16
15Asia9/30/2022$15$50$13Antartica$5$20
16Asia8/31/2022$18$60$15Antartica$6$24
17US7/31/2022$21$70$17Brazil$7$28
18US6/30/2022$24$80$19Brazil$8$32
19US5/31/2022$27$90$21Brazil$9$36
20US4/30/2022$30$100$23Brazil$10$40
21US3/31/2022$33$110$25Brazil$11$44
22US2/28/2022$36$120$27Brazil$12$48
23US1/31/2022$39$130$29Brazil$13$52
24US12/31/2021$42$140$31Brazil$14$56
25UK1/31/2023$45$150$33Poland$15$60
26UK12/31/2022$48$160$35Poland$16$64
27UK11/30/2022$51$170$37Poland$17$68
28UK10/31/2022$54$180$39Poland$18$72
29UK9/30/2022$57$190$41Poland$19$76
30UK8/31/2022$60$200$43Poland$20$80
31UK7/31/2022$63$210$45Poland$21$84
32Germany6/30/2022$66$220$47Austria$22$88
33Germany5/31/2022$69$230$49Austria$23$92
34Germany4/30/2022$72$240$51Austria$24$96
35Germany3/31/2022$75$250$53Austria$25$100
36Germany2/28/2022$78$260$55Austria$26$104
37Germany1/31/2022$81$270$57Austria$27$108
38Germany12/31/2021$84$280$59Austria$28$112
39Germany11/30/2021$87$290$61Austria$29$116
40Germany10/31/2021$90$300$63Austria$30$120
Sheet2
Cell Formulas
RangeFormula
B3:H8B3=SORT(FILTER(B11:H40,A11:A40=A3),2,-1)
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
A3List=$O$2:$O$5
Click to expand...

Greenbehindthecells said:
Hello,

I may not be on the right track for what you need, but I will try. I would reorganize the data so that I can use a continent dropdown cell A3 that will sort (by column C 'total') and filter the data. The 2 is the column indicator for sorting and the -1 is for largest to smallest order. I would use separate sheets as well but for mini excel in this example, I placed it all in one sheet. C10:H10 is the table where the data is, not the result which spills from cell B3 across.
=SORT(FILTER(B11:H40,A11:A40=A3),2,-1)

I hope this is helpful.

KED calc.xlsx
ABCDEFGHIJKLMN
1
2DateTotalTotal Merch1Total Merch2OriginationMerchandising 1Merchandising 2
3Asia8/31/2022186015Antartica624
49/30/2022155013Antartica520
510/31/2022124011Antartica416
611/30/20229309Antartica312
712/31/20226207Antartica28
87/31/20223105Antartica14
9
10ContinentDateTotalTotal Merch1Total Merch2OriginationMerchandising 1Merchandising 2
11Asia7/31/2022$3$10$5Antartica$1$4
12Asia12/31/2022$6$20$7Antartica$2$8
13Asia11/30/2022$9$30$9Antartica$3$12
14Asia10/31/2022$12$40$11Antartica$4$16
15Asia9/30/2022$15$50$13Antartica$5$20
16Asia8/31/2022$18$60$15Antartica$6$24
17US7/31/2022$21$70$17Brazil$7$28
18US6/30/2022$24$80$19Brazil$8$32
19US5/31/2022$27$90$21Brazil$9$36
20US4/30/2022$30$100$23Brazil$10$40
21US3/31/2022$33$110$25Brazil$11$44
22US2/28/2022$36$120$27Brazil$12$48
23US1/31/2022$39$130$29Brazil$13$52
24US12/31/2021$42$140$31Brazil$14$56
25UK1/31/2023$45$150$33Poland$15$60
26UK12/31/2022$48$160$35Poland$16$64
27UK11/30/2022$51$170$37Poland$17$68
28UK10/31/2022$54$180$39Poland$18$72
29UK9/30/2022$57$190$41Poland$19$76
30UK8/31/2022$60$200$43Poland$20$80
31UK7/31/2022$63$210$45Poland$21$84
32Germany6/30/2022$66$220$47Austria$22$88
33Germany5/31/2022$69$230$49Austria$23$92
34Germany4/30/2022$72$240$51Austria$24$96
35Germany3/31/2022$75$250$53Austria$25$100
36Germany2/28/2022$78$260$55Austria$26$104
37Germany1/31/2022$81$270$57Austria$27$108
38Germany12/31/2021$84$280$59Austria$28$112
39Germany11/30/2021$87$290$61Austria$29$116
40Germany10/31/2021$90$300$63Austria$30$120
Sheet2
Cell Formulas
RangeFormula
B3:H8B3=SORT(FILTER(B11:H40,A11:A40=A3),2,-1)
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
A3List=$O$2:$O$5
Click to expand...

Dear Greenbehindthecells,

Excellent work, effort and help. It actually opened my mind. Really appreciate it.

However, I would like to keep the format as how I sent in the message.

I thought that the best probably is to have two dropdown list, one with the continent of destination, and another one with the product (Merch. 1 and 2).
Like that, I will show a first table with TOTAL sales from all origins to ASIA, and below I will just have the tables with the origination countries.

It will simplify a lot the sheet and the visualization would be nice as well.

However, how to accomplish this?
Taking your data example, ASIA will have a TOTAL table and below just Antartica, however, how to automatically populate those tables? Meaning, if then US has two originations instead of only 1, the plan would be to choose US (A3), Merchandising 1 (A4), and to see a table with TOTAL sales to US, below e.g. origination Brazil and also let's say Argentina.

The tricky thing here is those tables need to be populated when the drop down is chosen and refreshed, and the data of those tables need to come from a database. Might need a code for it?

Again, thank you so much.
 
Upvote 0
mickeystanford_alumni said:
Dear Greenbehindthecells,

Excellent work, effort and help. It actually opened my mind. Really appreciate it.

However, I would like to keep the format as how I sent in the message.

I thought that the best probably is to have two dropdown list, one with the continent of destination, and another one with the product (Merch. 1 and 2).
Like that, I will show a first table with TOTAL sales from all origins to ASIA, and below I will just have the tables with the origination countries.

It will simplify a lot the sheet and the visualization would be nice as well.

However, how to accomplish this?
Taking your data example, ASIA will have a TOTAL table and below just Antartica, however, how to automatically populate those tables? Meaning, if then US has two originations instead of only 1, the plan would be to choose US (A3), Merchandising 1 (A4), and to see a table with TOTAL sales to US, below e.g. origination Brazil and also let's say Argentina.

The tricky thing here is those tables need to be populated when the drop down is chosen and refreshed, and the data of those tables need to come from a database. Might need a code for it?

Again, thank you so much.
Click to expand...
Hello,

Can you use xl2bb minisheet to show the data source? I misunderstood your post to needing ideas on how to sort the data so a formula would populate from a continent dropdown with descending order for totals. I am sorry I still might not be understanding how to help. If there is a sheet where the data is populated that you would like specific formulas to pull the data into your table format, I would need to know where the data is (column, rows in the data sheet) to try to attempt to make the formulas.


Thank you.
 
Upvote 0
Greenbehindthecells said:
Hello,

Can you use xl2bb minisheet to show the data source? I misunderstood your post to needing ideas on how to sort the data so a formula would populate from a continent dropdown with descending order for totals. I am sorry I still might not be understanding how to help. If there is a sheet where the data is populated that you would like specific formulas to pull the data into your table format, I would need to know where the data is (column, rows in the data sheet) to try to attempt to make the formulas.


Thank you.
Click to expand...
Hi, I will post something later, see if we can come up with a nice solution, apologies I couldn't answer before. Appreciate your feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Need assistance with auto disaggregation of data from monthly to weekly
Replies
1
Views
451
jajatidev
J
S
  • Question Question
Prevent pivot line chart dropping
Replies
8
Views
419
sharshra
S
M
  • Solved
Lookup formula
Replies
3
Views
454
Fluff
Fluff
G
  • Question Question
Copy some specific columns whose header is merge
2 3
Replies
26
Views
659
Joe4
Joe4
B
  • Question Question
Percent Change
Replies
4
Views
196
kvsrinivasamurthy
K

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,624
Latest member
gregg777

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