Just impossible - open for suggestions

[mickeystanford_alumni]

Hi guys, hope you are good.
I have a very complex formula that I need to build, not sure if using vba, just a long complex formula. I am struggling like never.

I will try to explain it well but it is not easy so lets go. To give some context it is a database I want to create with origin and destinations of sales. So, If I make a sale from Europe to the USA, then I want that number to be there.

I have 2 sheets:
- 1st sheet: it contains a matrix with data from different countries (those countries are the destinations were sales are done) and sales in each of the months. The countries are in a drop list button, which is one of the things that it is complicating my life that much. See below.
- ->2nd part (in the same sheet): it contains the origin where those sales were made. So when you choose your drop button list and you say Europe, then you have dozens of tables like the one below ("Table 2"). In this case, the table shows that the origin is USA, and all the other sheets contain the other origins such as Mexico, Chile, etc.

-2nd sheet: it contains the sheet I want to obtain. It has in column A Country name (this is country of destination). Then it includes the sales for each month. Here, what I want to do is and as you can see, I wrote 'Drop Down Button 2 that I will put'.

This is the fun part. Above each of the months, I want to choose every day the country of origin. So if I choose USA as origin, then automatically my excel goes to Sheet 1, chooses Europe from the drop list button (in yellow) [Why Europe? Because in Sheet 2, my first country is Europe ] then it goes to the tables below there, and it chooses origin USA, the value of 'Jan', in '2021/2022' and plugs the number. If instead, I want the origin to show Mexico, I choose Europe and then it takes the Mexican origin from the tables generated below. Tricky part is the range is kind of big, as every country of origin has a table with the years. (an Index match formula would be a way, however, the other ifs I want to apply are tricky).

Any small ideas are greatly appreciated as I am struggling so so so much.

Thank you guys.

SHEET 1

DROP LIST BUTTON WITH COUNTRIES

TOTAL

JAN

FEB

MAR

APR

MAY

JUN

JUL

AUG

SEP

OCT

NOV

DEC

28​

-​

-​

-​

-​

-​

-​

-​

-​

9​

4​

6​

9​

74​

9​

9​

9​

9​

9​

4​

1​

-​

4​

5​

6​

9​

47​

6​

6​

6​

6​

1​

4​

1​

-​

4​

4​

-​

9​

87​

6​

9​

9​

9​

9​

5​

9​

9​

4​

4​

5​

9​

60​

5​

5​

5​

5​

5​

5​

5​

4​

6​

5​

5​

5​

48​

1​

5​

5​

5​

5​

5​

5​

5​

5​

1​

5​

1​

55​

4​

4​

5​

5​

5​

5​

5​

5​

5​

4​

4​

4​

TABLES GENERATED ONCE YOU CHOOSE 'EUROPE' FROM THE DROP LIST BUTTON ABOVE

ORIGIN

USA

TOTAL

JAN

FEB

MAR

APR

MAY

JUN

JUL

AUG

SEP

OCT

NOV

DEC

TOTALUSA

2016/2017

TOTALUSA

2017/2018

TOTALUSA

2018/2019

TOTALUSA

2019/2020

TOTALUSA

2020/2021

TOTALUSA

2021/2022

TOTALUSA

2022/2023

THERE ARE DOZENS OF TABLES HERE WITH THE COUNTRY ORIGINS.

TABLE I WANT:

SALES

SALES

SALES

ETC

DROP DOWN BUTTON 2 THAT I WILL PUT

DROP DOWN

DROP DOWN

ETC

DESTINATION COUNTRY

JAN

FEB

MAR

APR

MAY

JUN

JUL

AUG

SEP

OCT

NOV

DEC

EUROPE

MEXICO

AUSTRALIA

.

.

.

 

[KRice]

I do not follow your explanation. If the data are coming from a database, do you have all of the data in a standardized format, in a single table, perhaps organized like this?

Book1
	A	B	C	D
1	Origin	Destination	Date	Qty or Sales
2	USA	Europe	1/15/2020	3
3	USA	Mexico	1/16/2020	4
4	Mexico	Europe	3/15/2020	2
Sheet5