Dependent lists/drop downs across a row

[smudgester]

Hi all,

I have been tasked with coming up with a spreadsheet for a client to keep track of some sales data.

On one of the sheets they want to have 3 columns which a sales person fills in from a pre-defined list of values - all simple enough so far. However, the underlying values are the same across all three columns but the same value can not be chosen in more than column; ideally with the lists in the other two columns changing as the value in any of the columns changes. The sales person also needs to be able to enter data in multiple rows.

So ... they want to provide a list of values, say, 'A,B,C,D' and they want a 3 column by unlimited row sheet where each cell contains one item from the list (or is empty) and a value in the list can only appear at most once per row but as many times as they want per column.

Any one got ideas if this possible ? And is it likely to be possible using just the List GUI ? Or likely to need some VBA type script/macro ?

 

[Peter_SSs]

Welcome to the MrExcel board!

I see you are using Excel 365 so if you have the new FILTER function, you could try this.

1. Somewhere (I have used the green cells) put a horizontal list of all the available choices.
   
   
2. Below that, in the yellow cell put the formula shown. The other results for that row should automatically "spill" across the rest of that row.
   
   
3. Now copy the yellow cell only down as far as you might need. All the other rows shouls also "spill across".
   
   At this point you have:
   
   

   A_MrExcel.xlsm
   	A	B	C	D	E	F	G	H	I	J	K
   1								A	B	C	D
   2								A	B	C	D
   3								A	B	C	D
   4								A	B	C	D
   5								A	B	C	D
   6								A	B	C	D
   7								A	B	C	D
   8								A	B	C	D
   9								A	B	C	D
   10								A	B	C	D
   DV List

   Cell Formulas
   Range	Formula
   H2:K10	H2	=FILTER(H$1:K$1,ISNA(MATCH(H$1:K$1,A2:C2,0)))
   Dynamic array formulas.

   
   
   
   
4. Now select A2:Cx (A2:C10 for me) and apply the Data Validation shown below.
   
   

   A_MrExcel.xlsm
   	A	B	C	D	E	F	G	H	I	J	K
   1								A	B	C	D
   2								A	B	C	D
   3								A	B	C	D
   4								A	B	C	D
   5								A	B	C	D
   6								A	B	C	D
   7								A	B	C	D
   8								A	B	C	D
   9								A	B	C	D
   10								A	B	C	D
   DV List

   Cells with Data Validation
   Cell	Allow	Criteria
   A2:C10	List	=$H2#

   
   
   
   
5. Use the drop-downs to enter values in column A:C, or you can delete/change entries. As you do, the corresponding list on that row will adjust to leave the remaining choices for that row.
   Here is the sheet after some entries have been made.
   
   
   
   
   
   
   
6. You could hide these helper columns if you want once the formulas have been populated down as far as you might want.

Note: This is fairly new to me and there does seem to be a bit of a glitch in that if you manually type a valid entry, the DV error message pops up but choosing 'Retry' did allow the entry for me

 

[smudgester]

Welcome to the MrExcel board!

I see you are using Excel 365 so if you have the new FILTER function, you could try this.

1. Somewhere (I have used the green cells) put a horizontal list of all the available choices.
   
   
2. Below that, in the yellow cell put the formula shown. The other results for that row should automatically "spill" across the rest of that row.
   
   
3. Now copy the yellow cell only down as far as you might need. All the other rows shouls also "spill across".
   
   At this point you have:
   
   

   A_MrExcel.xlsm
   	A	B	C	D	E	F	G	H	I	J	K
   1								A	B	C	D
   2								A	B	C	D
   3								A	B	C	D
   4								A	B	C	D
   5								A	B	C	D
   6								A	B	C	D
   7								A	B	C	D
   8								A	B	C	D
   9								A	B	C	D
   10								A	B	C	D
   DV List

   Cell Formulas
   Range	Formula
   H2:K10	H2	=FILTER(H$1:K$1,ISNA(MATCH(H$1:K$1,A2:C2,0)))
   Dynamic array formulas.

   
   
   
   
4. Now select A2:Cx (A2:C10 for me) and apply the Data Validation shown below.
   
   

   A_MrExcel.xlsm
   	A	B	C	D	E	F	G	H	I	J	K
   1								A	B	C	D
   2								A	B	C	D
   3								A	B	C	D
   4								A	B	C	D
   5								A	B	C	D
   6								A	B	C	D
   7								A	B	C	D
   8								A	B	C	D
   9								A	B	C	D
   10								A	B	C	D
   DV List

   Cells with Data Validation
   Cell	Allow	Criteria
   A2:C10	List	=$H2#

   
   
   
   
5. Use the drop-downs to enter values in column A:C, or you can delete/change entries. As you do, the corresponding list on that row will adjust to leave the remaining choices for that row.
   Here is the sheet after some entries have been made.
   
   View attachment 6157
   
   
   
6. You could hide these helper columns if you want once the formulas have been populated down as far as you might want.

Note: This is fairly new to me and there does seem to be a bit of a glitch in that if you manually type a valid entry, the DV error message pops up but choosing 'Retry' did allow the entry for me

Thanks Peter, I will give it a try and post back with how I get on.

A few questions first which I think I know the answer to:

a) In your step 3, I fill down to cover all the rows the sales person might enter data for, yes?

b) As well as hiding the helper columns do they have to be on the same sheet as where the data is entered ? Or can I have one set of helper columns on one (hidden) sheet which can be referenced by several other sheets ? I am assuming the helpers have to be on the same sheet ?

c) Adding/removing an option in the drop down list is simply done by adding/removing helper columns and changing the 'K' in all the formulas, right ?

 

[smudgester]

Also, sadly, I got the version of Excel wrong. And we are only using Excel 2016 not 365. And it would seem FILTER was not a valid function in the 2016 version

 

[Peter_SSs]

And it would seem FILTER was not a valid function in the 2016 version

That is correct and any alternative without filter will be considerably more cumbersome I think. I will ponder it a little. Please update your Account details.

However, I had pretty much prepared my answers to your questions so I will give them in case it is of use to somebody else or in case you get a change to 365.

a) In your step 3, I fill down to cover all the rows the sales person might enter data for, yes?

Fill down as far (or further) as the last row that you might ever want to use the Data Validation for the relevant sheet.

b) As well as hiding the helper columns do they have to be on the same sheet as where the data is entered ?

Helper columns can be on another sheet but you would need a separate set of helper columns for each other sheet you wanted to use them. More below.

c) Adding/removing an option in the drop down list is simply done by adding/removing helper columns and changing the 'K' in all the formulas, right ?

In the sample below, I have allowed for up to 10 values in each list. You can add or remove them from any position within the 10 cells as shown in the blue. No need to adjust the formulas or add/delete columns (unless expanding past 10)
So columns B:K are for helping on Sheet1 and N:W for Sheet2. The ranges do not need to be the same size unless the original list is going to be the same for each other sheet. In that case we could change to only having one coloured range but separate "spill ranges" for each sheet.

smudgester 2020-02-09.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Sheet1	A	B	C	D								Sheet2	A	B	C	D		F				J
2		A	B	C	D									A	B	C	D	F	J
3		A	B	C	D									A	B	C	D	F	J
4		A	B	C	D									A	B	C	D	F	J
5		A	B	C	D									A	B	C	D	F	J
6		A	B	C	D									A	B	C	D	F	J
7		A	B	C	D									A	B	C	D	F	J
8		A	B	C	D									A	B	C	D	F	J
9		A	B	C	D									A	B	C	D	F	J
10		A	B	C	D									A	B	C	D	F	J
Helper

Cell Formulas
Range	Formula
B2:E10	B2	=FILTER(B$1:K$1,(ISNA(MATCH(B$1:K$1,Sheet1!A2:C2,0)))*(B$1:K$1<>""))
N2:S10	N2	=FILTER(N$1:W$1,(ISNA(MATCH(N$1:W$1,Sheet2!A2:C2,0)))*(N$1:W$1<>""))
Dynamic array formulas.

The Data Validation on Sheet1 becomes ..

smudgester 2020-02-09.xlsm
	A	B	C
2
3
4
5
6
7
8
9
10
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A2:C10	List	=Helper!$B2#

Sheet2 ..

smudgester 2020-02-09.xlsm
	A	B	C
2
3
4
5
6
7
8
9
10
Sheet2

Cells with Data Validation
Cell	Allow	Criteria
A2:C10	List	=Helper!$N2#

 

[smudgester]

That is correct and any alternative without filter will be considerably more cumbersome I think. I will ponder it a little. Please update your Account details.

OK. The weird thing is, I am sure our company is running Office 365 but when I open Excel on machine PC it comes up as Excel 2016. Hopefully all the sales staff are running 365

However, I had pretty much prepared my answers to your questions so I will give them in case it is of use to somebody else or in case you get a change to 365.
<snip>

That is amazing, thank you

So columns B:K are for helping on Sheet1 and N:W for Sheet2. The ranges do not need to be the same size unless the original list is going to be the same for each other sheet. In that case we could change to only having one coloured range but separate "spill ranges" for each sheet.

So, if the original list has to be the same on every sheet then I simply need to change the formula, for N2, by replacing the FIRST N and W with B and K respectively, yes ?

 

[Peter_SSs]

Firstly, I had an error in the formula for cell N2 (& below) on the 'Helper' sheet in my previous post. It was referring to columns A:C on Sheet1 when it should have been referring to columns A:C on Sheet2. I have corrected it now.

So, if the original list has to be the same on every sheet then I simply need to change the formula, for N2, by replacing the FIRST N and W with B and K respectively, yes ?

You would replace all three references to N$1:W$1 with references to B$1:K$1.
However, if the original list was to be universal I would change the layout of Helper a little as follows.
The coloured cells indicate the columns that need to be left available either for the Master List or the spill values when a max of 10 values has been allowed for.
The sheet below is after several values have been entered in Sheet1 and Sheet2

smudgester 2020-02-10.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
1	Master List:	A	B	C	D		F				J				Sheet1												Sheet2
2															A	D	F										A	C	D	F	J
3															A	B	D	F	J								C	D	F	J
4															B	C	D	J									A	B	C	D	F	J
5															A	B	C	D	F	J							A	B	C	D	F	J
6															A	B	C	D	F	J							D	F	J
7															A	C	F										A	B	C	D	F	J
8															A	B	C	D	F	J							A	B	C	D	F	J
9															A	B	C	D	F	J							A	B	C	D	F	J
10															A	B	C	D	F	J							A	B	C	D	F	J
Helper

Cell Formulas
Range	Formula
O2:Q2,O8:T10,O7:Q7,O5:T6,O4:R4,O3:S3	O2	=FILTER(B$1:K$1,(ISNA(MATCH(B$1:K$1,Sheet1!A2:C2,0)))*(B$1:K$1<>""))
AA2:AE2,AA7:AF10,AA6:AC6,AA4:AF5,AA3:AD3	AA2	=FILTER(B$1:K$1,(ISNA(MATCH(B$1:K$1,Sheet2!A2:C2,0)))*(B$1:K$1<>""))
Dynamic array formulas.

smudgester 2020-02-10.xlsm
	A	B	C
1
2	J	C	B
3			C
4	A	F
5
6
7	B	D	J
8
9
10
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A2:C10	List	=Helper!$O2#

smudgester 2020-02-10.xlsm
	A	B	C
1
2		B
3		A	B
4
5
6	A	B	C
7
8
9
10
Sheet2

Cells with Data Validation
Cell	Allow	Criteria
A2:C10	List	=Helper!$AA2#