Dynamic Data Validation

[thbutterflycollector_]

Hi,

I have created a dynamic data validation formula which originally was set up to allow a single party to allocate a unique Reference Number to a particular line item from a cost ledger.

However I am now required to adjust this formula so that another party can also review the document and add a unique Reference Number.

In the attached I have DC001, DC002 and DC003 in the first table but in the second table I have DC004 (being the next available item to select), However the dropdown in the first table still shows DC004 as being able to be selected even though it has already been picked by the second reviewer. Can anyone help resolve?

Sample.xlsx
	B	C	D	E	F	G	H
2	Type	Helper	DC #		Type	Helper	DC #
3	X	True			X	True
4	X	True			Fee	True
5	X	True			Disallowed	False	DC004
6	Disallowed	False	DC001			True
7	Disallowed	False	DC002			True
8	Disallowed	False	DC003			True
9	Disallowed	False				True
10		True				True
Raw Data

Cell Formulas
Range	Formula
C3:C10,G3:G10	C3	=IF(B3="Disallowed","False","True")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:H1048343	Expression	=$A3="R"	text	NO
D3:D10,H3:H10	Expression	=C3="True"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D2	List	=Register!$B$7#
D3	List	=IF(C3="False",Register!#REF!#)
D4:D10	List	=IF(C4="False",Register!$B$7#)
H2	List	=Register!$B$7#
H3	List	=IF(G3="False",Register!B8#)
H4:H10	List	=IF(G4="False",Register!$B$7#)

Sample.xlsx
	B	C	D
6		DC Ref.	Identified By
7	DC004	DC001
8	DC005	DC002
9	DC006	DC003
10	DC007	DC004
11	DC008	DC005
12	DC009	DC006
13	DC010	DC007
14	DC011	DC008
15	DC012	DC009
16	DC013	DC010
Register

Cell Formulas
Range	Formula
B7:B53	B7	=FILTER(Register!$C$7:$C$56,COUNTIF('Raw Data'!D:D,Register!$C$7:$C$56)=0)
Dynamic array formulas.

 

[kevin9999]

One way:

=FILTER(C7:C56,(COUNTIF('Raw Data'!D:D,C7:C56)=0)*(COUNTIF('Raw Data'!H:H,C7:C56)=0))&""

 

[thbutterflycollector_]

Just looking at this further I have added another column to compare whether applied = allowed. If there is the joint agreement column shows "Yes". I've then modified the formula in second Column DC# to check if this is true. If it is then it yields the same DC reference as that of the first DC# Column. If not the next sequential number is selected from the table on Tab 2:

Sample1.xlsx
	B	C	D	E	F	G	H	I	J	K
3	Applied	Cost Ledger Allocation	Helper	DC #		Allowed	Helper	Joint Agreement	DC #
4	X	X	True			X	True	N/A
5	X	X	True			Fee	True	N/A
6	Disallowed	X	False	DC006		Disallowed	False	Yes		should be DC006 but allows me to select DC004 then DC007 etc.
7	X	X	True			X	True	N/A
8	X	X	True			X	True	N/A
9	X	X	True			X	True	N/A
10	X	X	True				True	N/A
11		X	True				True	N/A
12		X	True				True	N/A
13		X	True				True	N/A
14		X	True			Disallowed	False	N/A	DC004
15		X	True				True	N/A
16		X	True				True	N/A
17	Disallowed	X	False	DC001		Disallowed	False	Yes		Should be DC001
18	Disallowed	X	False	DC002		Disallowed	False	Yes		Should be DC002
19	Disallowed	X	False	DC003		Disallowed	False	Yes		Should be DC003
20	Disallowed	X	False	DC005		Disallowed	False	Yes		Should be DC005
1

Cell Formulas
Range	Formula
H4:H20	H4	=IF(G4="Disallowed","False","True")
I4:I20	I4	=IF(AND(H4="False",D4="False"),"Yes","N/A")
D4:D20	D4	=IF(B4="Disallowed","False","True")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:J1048333	Expression	=$A4="R"	text	NO
J4:J20	Expression	=H4="True"	text	NO
E4:E20	Expression	=D4="True"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E3	List	='2'!$B$4#
E4	List	=IF(D4="False",'2'!#REF!#)
E5:E20	List	=IF(D5="False",'2'!$B$4#)
J3	List	='2'!$B$4#
J4:J20	List	=IF($I14="Yes",$E4, '2'!$B$4#)

One way:

=FILTER(C7:C56,(COUNTIF('Raw Data'!D:D,C7:C56)=0)*(COUNTIF('Raw Data'!H:H,C7:C56)=0))&""

Problem Solved! Thank you Kevin.