Help with building a formula

[zinah]

Hi,

I'm wondering if someone can help me with building the right formula that can accomplish below scenario:

I have drop-down list that has Regions, each location has multiple Division. What I want is when I choose one of the regions, the multiple divisions will pop out. Here's the tricky part, each region doesn't necessarily to have the same number of divisions. How can I accomplish that?

Below screenshot can help with what I need to accomplish.

 

[Fluff]

What version of Excel are you using?
Please update your account details to show this, as it affects what functions are available to you.

 

[zinah]

What version of Excel are you using?
Please update your account details to show this, as it affects what functions are available to you.

Sorry, I missed it, it's Office 365 and I wish I have newer as I saw new function which is Filter that can help me accomplishing my task.

 

[Fluff]

Are you saying that you don't have the Filter function yet?

Also please remember to update your account details.

 

[zinah]

Are you saying that you don't have the Filter function yet?

Also please remember to update your account details.

No I don't have this magical function and I just updated my account details
Is there any way you can help?

 

[Fluff]

Oh, well you should get the new functions in a few weeks.
In the mean time how about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I
1
2	Division	Region	Revenue			Region	South America
3	Utility	North America	34,604
4	Utility	South America	18,197			Utility	South America	18,197
5	Utility	Asia	44,475			Productivity	South America	46,790
6	Utility	Europe	26,675			Game	South America	46,273
7	Utility	Australia	31,542
8	Productivity	North America	18,959
9	Productivity	South America	46,790
10	Productivity	Asia	42,672
11	Productivity	Europe	19,351
12	Productivity	Australia	44,021
13	Game	North America	11,150
14	Game	South America	46,273
15	Game	Asia	44,414
16	Game	Europe	34,955
17
18
19
Input

Cell Formulas
Range	Formula
F4:H8	F4	=IFERROR(INDEX(A$3:A$50,AGGREGATE(15,6,(ROW($B$3:$B$50)-ROW($B$3)+1)/($B$3:$B$50=$G$2),ROWS(F$4:F4))),"")

Cells with Data Validation
Cell	Allow	Criteria
G2	List	=$B$3:$B$7

 

[zinah]

Oh, well you should get the new functions in a few weeks.
In the mean time how about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I
1
2	Division	Region	Revenue			Region	South America
3	Utility	North America	34,604
4	Utility	South America	18,197			Utility	South America	18,197
5	Utility	Asia	44,475			Productivity	South America	46,790
6	Utility	Europe	26,675			Game	South America	46,273
7	Utility	Australia	31,542
8	Productivity	North America	18,959
9	Productivity	South America	46,790
10	Productivity	Asia	42,672
11	Productivity	Europe	19,351
12	Productivity	Australia	44,021
13	Game	North America	11,150
14	Game	South America	46,273
15	Game	Asia	44,414
16	Game	Europe	34,955
17
18
19
Input

Cell Formulas
Range	Formula
F4:H8	F4	=IFERROR(INDEX(A$3:A$50,AGGREGATE(15,6,(ROW($B$3:$B$50)-ROW($B$3)+1)/($B$3:$B$50=$G$2),ROWS(F$4:F4))),"")

Cells with Data Validation
Cell	Allow	Criteria
G2	List	=$B$3:$B$7

I really look forward to get the new functions as they are really helpful! Thank you for your help, a favor though, I'm trying to understand the syntax of the Aggregate function and couldn't understand the (15,6) is 15 stand for number of rows?

 

[Fluff]

15 means small & 6 means ignore errors.
Have a look here

AGGREGATE Function

The Excel AGGREGATE function returns a aggregate calculation like AVERAGE, COUNT, MAX, etc., optionally ignoring hidden rows and errors. A total of 19 operations are available, specified by function number in the first argument (see table for options).

exceljet.net

 

[zinah]

15 means small & 6 means ignore errors.
Have a look here

AGGREGATE Function

The Excel AGGREGATE function returns a aggregate calculation like AVERAGE, COUNT, MAX, etc., optionally ignoring hidden rows and errors. A total of 19 operations are available, specified by function number in the first argument (see table for options).

exceljet.net

Thank you SO MUCH! You're the best!

 

[Fluff]

You're welcome & thanks for the feedback.