Creating a drop down list (with formula?)

[DarkSmile]

Hi,

I need some help with a drop down list, if been googling sinds yesterday, tried a bunch of stuff but unable to get what i'm trying to achieve.
I have a list:

Test-new.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M
4	BRC ASDA	BRC Agents & Brokers	BRC Packaging Materials	BRC S&D	BRC Food	FSSC 22000	BRC GFCP	IFS Broker	IFS Logistics	IFS Food	IFS Wholesalte Cash & Carry	IFS Global Markets Food
5									x
6										x
7										x
8										x
9						x
10						x
11						x
12						x
13						x
14										x
15					x
16										x
17						x
18												x
19						x
20										x
21					x					x
22						x
23				x
24						x
25					x					x
26					x					x
Follow-up

Cell Formulas
Range	Formula
B5:M26	B5	=IFNA(INDEX(Certivin!$M:$M,MATCH(1,($A5=Certivin!$A:$A)*(B$4=Certivin!$C:$C),0)),"")

I want a drop down list based on where there is an "x", example:

Row 5: has an "x" in J, so the drop down should show "IFS Logistics"
Row 26: has "x" in F, K so the drop down should show "BRC Food, IFS Food" underneath each other (not next to each other).

I could just select the full row but I don't want blanks in the drop down:

If got a formula to separate them with a comma, but now I need to get this in to a drop down as follows

=TEXTJOIN(", ",,IF(B26:M26="x",$B$4:$M$4,""))

Example row 26:
BRC Food
IFS Food
...

And not as BRC Food, IFS Food

I tried the following formulas, but without success.

=INDIRECT(SUBSTITUTE(BB26,",",""))

=SUBSTITUTE(BB26,",","")

Is there anything else I can try? It's not limited to 2 "x" per lane.

Thank you
David

 

[jasonb75]

What you asking for can not be done with a formula in the data validation list box. You would need to produce the condensed list in a worksheet (1 list for each row).

The other option would be to use vba to create the list without a formula, perhaps with a selection change event.

 

[DarkSmile]

What you asking for can not be done with a formula in the data validation list box. You would need to produce the condensed list in a worksheet (1 list for each row).

The other option would be to use vba to create the list without a formula, perhaps with a selection change event.

I'm not able to write vba, are you able to help me with such code?

 

[jasonb75]

I will be able to but it's taking me a bit longer to get it done than anticipated, I'm away from home and don't have my laptop with me. With a phone, my ability is limited to writing simple formulas.

I can't see which version of excel you're using on your profile, but if you have the FILTER function then it may work with a formula. There are limits on the types of formula that can be used with data validation dropdowns and I had it in mind that it didn't work when i tried it once before but could giving it a go before trying vba.

=TRANSPOSE(FILTER($B$4:$M$4,B26:M26="x"))

 

[DarkSmile]

I will be able to but it's taking me a bit longer to get it done than anticipated, I'm away from home and don't have my laptop with me. With a phone, my ability is limited to writing simple formulas.

I can't see which version of excel you're using on your profile, but if you have the FILTER function then it may work with a formula. There are limits on the types of formula that can be used with data validation dropdowns and I had it in mind that it didn't work when i tried it once before but could giving it a go before trying vba.

=TRANSPOSE(FILTER($B$4:$M$4,B26:M26="x"))

Sorry late response, I'm using Excel 2016. As a normal formula this works, but in data validation it does not.

 

[jasonb75]

With that error it should work if you click yes. That just means that there is no x in that row when you're setting it up.

Also, I would suggest that you double check that the formula does work in the sheet. That formula wouldn't work in Excel 2016.

 

[DarkSmile]

With that error it should work if you click yes. That just means that there is no x in that row when you're setting it up.

Also, I would suggest that you double check that the formula does work in the sheet. That formula wouldn't work in Excel 2016.

The formula does work in a cell, just not in the data validation thing.

 

[jasonb75]

I'll set up a test sheet when I get home so I can check what is happening with it. (Thursday evening uk time).
There are some limits on formulas that can be used in validation but usually it won't let you enter any that can't be used.

 

[jasonb75]

Sorry, it took a bit longer than expected before I had chance to look at this. As far as the error goes, it appears that the formula can be entered as a validation rule but will not allow the dropdown to function correctly. It works for manually entered data (typing entries into the validated cells instead of using dropdowns), but that appears to be the limit.

The mini sheet below shows a slightly different way of doing it, by using a helper range (if acceptable) that can be hidden once the sheet is set up. You only need to enter the formulas into column O, they will fill right dynamically as needed. Then set up the data validation referring to =O5# as the dropdown source for N5 and apply it to the rest of the column as needed.

Note that there need to be enough empty columns for the filter formula to use if there are more x's in the row, ideally I would keep columns O:Z empty so that there are the same number of empty columns as there are in the table. If you have other data in those columns already then you can start farther to the right as opposed to moving what you already have. That is just down to your personal preference.

I've gone for the simpler method first, if you can't use the helper range method then I'll sort out the vba alternative for you sometime over the next couple of days (will wait for you to confirm if it is still required first).

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
4	BRC ASDA	BRC Agents & Brokers	BRC Packaging Materials	BRC S&D	BRC Food	FSSC 22000	BRC GFCP	IFS Broker	IFS Logistics	IFS Food	IFS Wholesalte Cash & Carry	IFS Global Markets Food
5									x					IFS Logistics
6										x				IFS Food
7										x				IFS Food
8										x				IFS Food
9						x								FSSC 22000
10						x								FSSC 22000
11						x								FSSC 22000
12						x								FSSC 22000
13						x								FSSC 22000
14										x				IFS Food
15					x									BRC Food
16										x				IFS Food
17						x								FSSC 22000
18												x		IFS Global Markets Food
19						x								FSSC 22000
20										x				IFS Food
21					x					x				BRC Food	IFS Food
22						x								FSSC 22000
23				x										BRC S&D
24						x								FSSC 22000
25					x					x				BRC Food	IFS Food
26					x					x				BRC Food	IFS Food
Sheet1

Cell Formulas
Range	Formula
O5:O20,O22:O24,O25:P26,O21:P21	O5	=FILTER($B$4:$M$4,B5:M5="x")

Cells with Data Validation
Cell	Allow	Criteria
N5:N26	List	=O5#

Hope this helps.

 

[DarkSmile]

Works great! Thank you for your help and patience.