Dependent dropdownlist on Excel table with subheaders

[BURTPRI]

Hello everybody! This is my first time posting a thread and I really hope someone here can help me quick enough.
I have the following "table" (in quotes bc I couldn't format as an actual Excel table) that brings 3 identical subheaders categories (ADDRESS, EMPLOYEES and EMPLOEMAILS) to each header (the Company).
I need to create three cells with dropdown lists for each subheader category based on the main dropdown list where the user chooses the Company.
Please note that Row 1 brings the headers, Row 2 the subheaders and Raws 3 and 4 the data collected.
I hope I was able to express my challenge well.
Thank you very much if you read this far.

 

[CA_Punit]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2		Company 1					Company 2						Company 1
3		Address	Employees	Employees Mail			Address	Employees	Employees Mail				Company 2
4		qq	Employees 1	Employeesmail 1			aa	Comp2a	employeesmail2a
5		asd	Employees 2	Employeesmail 2			bb	Comp2b	employeesmail2b
6
7
8				Company	Company 2
9				Address	aa
10				Employees	Comp2a
11				Employees Mail	employeesmail2a
12
13
14
15
16
17
18
19
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:A142	Expression	=AND(A3="P",M3>=69,M3<=82,O3=5)	text	NO
A2	Expression	=AND(A2="P",M2>=69,M2<=82,O2=5)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E8	List	=$M$2:$M$3
E9	List	=IF(E8="Company 1",Add_Comp1,Address_Com2)
E10	List	=IF(E8="Company 1",Emp_Name_1,Empl_Com2)
E11	List	=IF(E8="Company 1",Mail_Comp1,Mail_Com2)

 

[CA_Punit]

Please dont copy the Cell with Conditional Formatting..
However i have few issue in the structure.

 

[nardagus]

Another atempt.

It uses dynamic ranges, but I will make your worksheet more flexible. You just need to add another Company with the same column structure and that's all. No changes to DV needed. Well. At least to Address, Employees and Emploemail DVs.

Zeszyt1.xlsx
	A	B	C	D	E	F
1	Company1			Company2
2	Address	Employees	Emploemails	Address	Employees	Emploemails
3	Address1	Employees1	Emploemails1	Address3	Employees3	Emploemails3
4	Address2	Employees2	Emploemails2	Address4	Employees4	Emploemails4
5
6	Company	Company1
7	Address
8	Employees
9	Emploemail
Arkusz1

Cells with Data Validation
Cell	Allow	Criteria
B6	List	Company1;Company2
B7	List	=OFFSET(INDIRECT(ADDRESS(3;MATCH(B6;A1:F1;0);1));0;0;2;1)
B8	List	=OFFSET(INDIRECT(ADDRESS(3;MATCH(B6;A1:F1;0);1));0;1;2;1)
B9	List	=OFFSET(INDIRECT(ADDRESS(3;MATCH(B6;A1:F1;0);1));0;2;2;1)

 

[CA_Punit]

Hi, @nardagus

The formula is not returning the values in B7. Kindly help.

 

[nardagus]

Hi, @CA_Punit
Hmmm strange. It is returning values in my worksheet...

P.s. If there are to be more companies, then MATCH formula should have bigger range to check.

P.s.2: Question is if Company1 and Company2 are in the first cell of the range? In this case in A1 and D1? I assumed there is the same data structure for all companies.

 

[BURTPRI]

Hi @nardagus thank you very very much for your answer I really appreciate. I forgot to mention tho that there will be other companies added to this table as well... Probably more 20 companies... So in this case how should I use the MATCH formula?

 

[Fluff]

Cross posted Dependent dropdown list with subheader categories

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[nardagus]

Hi @BURTPRI

No problem. Glad to help.

Well I see two things that need to be done/clarify.

You want around 20 companies. I was also thinking that there might be different number of employees and their email addresses across all companies. Am I right?

Ok. So there are two solutions for that

1) Less flexible, but more simple formula (which leads to easier debugging)
2) More flexible, but bigger and more complex formula

First solution assumes that there will be some max or less number of employees. You will assume that the biggest company will have a 100 employees. This will be max range used for all dropdown entries (In other words it will display values from a range of 100 cells). This however has one flaw. It will render empty entries in dropdown:

Second solution can also assume that the biggest company is a 100 employees large, but it will analyze this range and display only non empty cells:

So this how my data looks like now:

Zeszyt1.xlsx
	A	B	C	D	E	F	G	H
1			Company1			Company2
2			Address	Employees	Emploemails	Address	Employees	Emploemails
3			Address1	Employees1	Emploemails1	Address3	Employees3	Emploemails3
4			Address2	Employees2	Emploemails2	Address4	Employees4	Emploemails4
5
6	Company	Company1
7	Address	Address1
8	Employees	Employees1
9	Emploemail	Emploemails1
Arkusz1

Address DV formula:
1st solution:
=OFFSET(INDIRECT(ADDRESS(3;MATCH(B6;A1:BR1;0);1));0;0;100;1)
Number 100 here assumes a 100 of employees across all companies.
MATCH(B6;C1:BR1;0) includes a range C1 to BR1 where you will place companies data. If there will be more companies you just need to change the range.

2nd solution
=OFFSET(INDIRECT(ADDRESS(3;MATCH(B6;C1:BR1;0);1));0;0;COUNTA(INDIRECT(ADDRESS(3;MATCH(B6;C1:BR1;0);1)&":"&ADDRESS(100;MATCH(B6;C1:BR1;0);1)));1)

Here all you need to do is to change range C1:BR1 should there be more companies.

Also remember two things:

1) Cell B6 is the one where DV fomulas check which company was chosen. If you move Company somewhere else you need to change it to a new cell address
2) Cells with DV must not be in a range of employees. So if there are 100 employees (so range C3 to C103) you must place DV in C104. Or event better. Place them in different column. Not containing company data. Otherwise you will have circular references error and DV won't work

I hope I explained it clearly.

 

[BURTPRI]

WOW once again thank you very very much for your patience and yes, it was very clear!

Im embarrassed to say that but... I have my data exactly like yours, my reference Company is in cell B6 as you said, but I'm trying to insert the formulas on the 'Source' field of Data Validation dialog box and it says there's a problem with the formula. I tried to put both formulas also in regular cells as well just in case and still says there's a problem with the formula. Any idea of what am I doing wrong...?