Multiple Drop-down menu

[nikolacm]

I have a table which includes number of rooms, type, level, surface and view. This table consists of 22 rows as the total number of its units. I try to prepare a table that will summarize the total number of units in terms of room type and view. Since each room type has different view I want to prepare a drop down menu for every view category in terms of each room type. For example room type A has rooms with both garden and sea view. As a result the drop down menu should include garden and sea view. Any ideas ?

s/n	Room Number	Room Type	Level	Surface (m²)	View			Room Type	View	Units
1	113	113	1st Floor	55	Pool			A
2	G20	A	Ground Floor	23	Garden			A1
3	G21	A	Ground Floor	23	Garden			B
4	G22	A	Ground Floor	23	Garden			B1
5	120	A	1st Floor	23	Sea			C
6	220	A	2nd Floor	23	Sea			C1
7	119	A1	1st Floor	23	Sea			D
8	219	A1	2nd Floor	23	Sea			Family
9	319	A1	3rd Floor	23	Sea			113
10	110	B	1st Floor	26,5	Pool			Suite
11	111	B	1st Floor	26,5	Pool			F
12	114	B	1st Floor	26,5	Pool			F1
13	210	B	2nd Floor	26,5	Pool			E
14	211	B	2nd Floor	26,5	Pool			Z
15	212	B	2nd Floor	26,5	Pool			H
16	213	B	2nd Floor	26,5	Pool
17	214	B	2nd Floor	26,5	Pool
18	310	B	3rd Floor	26,5	Pool
19	311	B	3rd Floor	26,5	Pool
20	312	B	3rd Floor	26,5	Pool
21	313	B	3rd Floor	26,5	Pool
22	314	B	3rd Floor	26,5	Pool

 

[shinigamilight]

You know how to use the name manager & datavalidation?

 

[nikolacm]

You know how to use the name manager & datavalidation?

I am not totaly aware of how to use these functions.

 

[etaf]

i have used a pivot table
although - if you posted the expected results - textjoin, with filter may work

not sure how you want the dropdown to work - i will post later an example - using filter

room-view-ETAF.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	s/n	Room Number	Room Type	Level	Surface (m²)	View
2	1	113	113	1st Floor	55	Pool					Room Type	View	Count of View
3	2	G20	A	Ground Floor	23	Garden					113	Pool	1
4	3	G21	A	Ground Floor	23	Garden					A	Garden	3
5	4	G22	A	Ground Floor	23	Garden						Sea	2
6	5	120	A	1st Floor	23	Sea					A1	Sea	3
7	6	220	A	2nd Floor	23	Sea					B	Pool	13
8	7	119	A1	1st Floor	23	Sea					Grand Total		22
9	8	219	A1	2nd Floor	23	Sea
10	9	319	A1	3rd Floor	23	Sea
11	10	110	B	1st Floor	26,5	Pool
12	11	111	B	1st Floor	26,5	Pool
13	12	114	B	1st Floor	26,5	Pool
14	13	210	B	2nd Floor	26,5	Pool
15	14	211	B	2nd Floor	26,5	Pool
16	15	212	B	2nd Floor	26,5	Pool
17	16	213	B	2nd Floor	26,5	Pool
18	17	214	B	2nd Floor	26,5	Pool
19	18	310	B	3rd Floor	26,5	Pool
20	19	311	B	3rd Floor	26,5	Pool
21	20	312	B	3rd Floor	26,5	Pool
22	21	313	B	3rd Floor	26,5	Pool
23	22	314	B	3rd Floor	26,5	Pool
Sheet1

this share copy will only be available for a few days

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[etaf]

so this version
has a dropdown for room type
then gives the views available and a count of the units

so i use a datavalidation list for the dropdown l2 - based on the list in column S

then i use a filter to filter the results to only those with a room type from the drop down - then unique so it only shows once

then a countifs() to count how many units of the room type and view in the list

room-view-ETAF.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	s/n	Room Number	Room Type	Level	Surface (m²)	View						Room Type	View	Units					Data Val List
2	1	113	113	1st Floor	55	Pool		Room Type	View	Count of View		A	Garden	3					113
3	2	G20	A	Ground Floor	23	Garden		113	Pool	1			Sea	2					A
4	3	G21	A	Ground Floor	23	Garden		A	Garden	3									A1
5	4	G22	A	Ground Floor	23	Garden			Sea	2									B
6	5	120	A	1st Floor	23	Sea		A1	Sea	3
7	6	220	A	2nd Floor	23	Sea		B	Pool	13
8	7	119	A1	1st Floor	23	Sea		Grand Total		22
9	8	219	A1	2nd Floor	23	Sea
10	9	319	A1	3rd Floor	23	Sea
11	10	110	B	1st Floor	26,5	Pool
12	11	111	B	1st Floor	26,5	Pool
13	12	114	B	1st Floor	26,5	Pool
14	13	210	B	2nd Floor	26,5	Pool
15	14	211	B	2nd Floor	26,5	Pool
16	15	212	B	2nd Floor	26,5	Pool
17	16	213	B	2nd Floor	26,5	Pool
18	17	214	B	2nd Floor	26,5	Pool
19	18	310	B	3rd Floor	26,5	Pool
20	19	311	B	3rd Floor	26,5	Pool
21	20	312	B	3rd Floor	26,5	Pool
22	21	313	B	3rd Floor	26,5	Pool
23	22	314	B	3rd Floor	26,5	Pool
24
25
Sheet1

Cell Formulas
Range	Formula
M2:M3	M2	=UNIQUE(FILTER(F2:F23,C2:C23=L2))
S2:S5	S2	=UNIQUE(C2:C23)
N2:N16	N2	=IF(COUNTIFS($C$2:$C$23,$L$2,$F$2:$F$23,M2)=0,"",COUNTIFS($C$2:$C$23,$L$2,$F$2:$F$23,M2))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
L2	List	=$S$2:$S$5

also updated dropbox version

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[nikolacm]

so this version
has a dropdown for room type
then gives the views available and a count of the units

so i use a datavalidation list for the dropdown l2 - based on the list in column S

then i use a filter to filter the results to only those with a room type from the drop down - then unique so it only shows once

then a countifs() to count how many units of the room type and view in the list

room-view-ETAF.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	s/n	Room Number	Room Type	Level	Surface (m²)	View						Room Type	View	Units					Data Val List
2	1	113	113	1st Floor	55	Pool		Room Type	View	Count of View		A	Garden	3					113
3	2	G20	A	Ground Floor	23	Garden		113	Pool	1			Sea	2					A
4	3	G21	A	Ground Floor	23	Garden		A	Garden	3									A1
5	4	G22	A	Ground Floor	23	Garden			Sea	2									B
6	5	120	A	1st Floor	23	Sea		A1	Sea	3
7	6	220	A	2nd Floor	23	Sea		B	Pool	13
8	7	119	A1	1st Floor	23	Sea		Grand Total		22
9	8	219	A1	2nd Floor	23	Sea
10	9	319	A1	3rd Floor	23	Sea
11	10	110	B	1st Floor	26,5	Pool
12	11	111	B	1st Floor	26,5	Pool
13	12	114	B	1st Floor	26,5	Pool
14	13	210	B	2nd Floor	26,5	Pool
15	14	211	B	2nd Floor	26,5	Pool
16	15	212	B	2nd Floor	26,5	Pool
17	16	213	B	2nd Floor	26,5	Pool
18	17	214	B	2nd Floor	26,5	Pool
19	18	310	B	3rd Floor	26,5	Pool
20	19	311	B	3rd Floor	26,5	Pool
21	20	312	B	3rd Floor	26,5	Pool
22	21	313	B	3rd Floor	26,5	Pool
23	22	314	B	3rd Floor	26,5	Pool
24
25
Sheet1

Cell Formulas
Range	Formula
M2:M3	M2	=UNIQUE(FILTER(F2:F23,C2:C23=L2))
S2:S5	S2	=UNIQUE(C2:C23)
N2:N16	N2	=IF(COUNTIFS($C$2:$C$23,$L$2,$F$2:$F$23,M2)=0,"",COUNTIFS($C$2:$C$23,$L$2,$F$2:$F$23,M2))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
L2	List	=$S$2:$S$5

also updated dropbox version

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Many thanks. This was what I was looking for. The sollution seems to be much simpler compared with my thoughts when I was preparing the case.

 

[etaf]

you are welcome