Drop-down menus in tables and removing used items from that menu

[ctroxtell]

Hey guys, I'm hoping someone can help get me on the right track. I'm working on an Excel sheet that has 100+ addresses on it with employees on the sheet. I'm trying to make it so the drop-down menu will only allow the list item to be selected one time. The sheet has 10 tables, 1 for each staff member with a column in a table labeled Address, then I've created a table that holds my entire list of addresses. How can I hide the addresses that have already been selected from the drop down menu? Currently using Excel 365, I was experimenting and had got it partially working with COUNTIF but it wouldn't translate when I selected the entire address column

 

[ctroxtell]

Properties.xlsx
	R	S	T	U	V	W	X	Y	Z
7	Open						Address	Sq ft	FS
8							100 Johnson Ridge Rd (Elkin)	4,262	FS
9							100 Kimel Forest Dr (Winston)	72,478	FS
10	Bldg #	Address	S/F				100 Regent Dr (Winston)	37,785	LO
11	R	2000 Cloverdale Ave (Winston)	5,961				1002 N Church St, Ste 203 (Greensboro)	1,546	R
12	FS	2200 Cloverdale Ave (Winston)	75,816				101 Charlois Blvd (Winston)	15,676	FS
13	FS	403 S Hawthorne Rd (Winston)	4,502				1010 Bethania Rural Hall Rd (Rural Hall)	1,025	FS
14	R	1920 Queen St (Winston)	2,506				1014 N Elm St (Greensboro)	3,547	FS
15	FS	910 Worth St (Mt. Airy)	17,860				1015 Worth St (Mt. Airy)	2,520	FS
16	R	231 Melrose St (Winston)	10,552				1038 Bethania Rural Hall Rd (Rural Hall)	9,800	FS
17	6	Total Square Footage FS	117,197				109/111 Gateway Center Dr (Kernersville)	11,292	FS
18	LO	1126 North Church Street	4,792				111 Hanestown Ct (Winston)	16,919	FS
19	1	Total Square Footage LO	4,792				1126 North Church Street	4,792	LO
20							113 Market Place Dr (Mocksville)	2,700	LO
21	7	Grand Total	121,989				1132 N Church St (Greensboro)	17,500	LO
Su

Cell Formulas
Range	Formula
R11:R16,R18	R11	=IFERROR(VLOOKUP([@Address],Table1,3,FALSE),"")
R17	R17	=COUNTA(R11:R16)
R19	R19	=COUNTA(R18)
T11:T16,T18	T11	=IFERROR(VLOOKUP([@Address],Table1,2,FALSE),"")
T17	T17	=SUM(T11:T16)
T19	T19	=SUM(T18)
R21	R21	=SUM(R17+R19)
T21	T21	=SUM(T17,T19)

Cells with Data Validation
Cell	Allow	Criteria
S11:S16	List	=$X$8:$X$107
S18	List	=$X$8:$X$107

Here is a sample of what I'm working with. Is this a feasible option or is there a better way to achieve this result? The address in the table listed as open has data validation running off of the address table to the right.

 

[Peter_SSs]

I have used smaller sample data but try like this using a helper column (AB for me)
Table1 is the right hand table with Table2 on the left.

ctroxtell.xlsm
	R	S	T	U	V	W	X	Y	Z	AA	AB
7							Address	Sq ft	FS
8							Address 1	4,262	FS		Address 1
9							Address 2	72,478	FS		Address 2
10	Bldg #	Address	S/F				Address 3	37,785	LO		Address 4
11							Address 4	1,546	R		Address 6
12	FS	Address 5	15676				Address 5	15,676	FS		Address 7
13							Address 6	1,025	FS		Address 8
14	LO	Address 3	37785				Address 7	3,547	FS
15	FS	Address 10	11292				Address 8	2,520	FS
16							Address 9	9,800	FS
17							Address 10	11,292	FS
18	FS	Address 9	9800
19
20
Sheet1

Cell Formulas
Range	Formula
AB8:AB13	AB8	=FILTER(Table1[Address],ISNA(MATCH(Table1[Address],Table2[Address],0)),"")
R11:R18	R11	=IFERROR(VLOOKUP([@Address],Table1,3,FALSE),"")
T11:T18	T11	=IFERROR(VLOOKUP([@Address],Table1,2,FALSE),"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
S11:S18	List	=AB$8#

 

[ctroxtell]

Thanks! That works for the first table called "Open" but I have 9 more tables I need to check the addresses on to see if those properties are assigned. Is it possible to add them into this formula?

 

[Peter_SSs]

Could you make up a sample with say 3 small tables partially filled in, post that with XL2BB, and explain what is required in relation to that new sample data?

 

[ctroxtell]

Su.xlsx
	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
7	Employee 1				Employee 3						Address	Sq ft	FS
8											Address 1	4,262	FS
9											Address 2	72,478	FS
10	Bldg #	Address	S/F		Bldg #	Address	S/F				Address11	37,785	LO
11	R	Address 1	54,000		R	Address 1	5,961				Address 2	1,546	R
12	FS	Address 1	218,384		FS	Address 2	75,816				Address #	15,676	FS
13	FS	Address 1	20,000		FS	Address 2	4,502				Address #	1,025	FS
14	3	Total Square Footage FS	292,384		R	Address 3	2,506				Address #	3,547	FS
15					FS	Address 4	17,860				Address #	2,520	FS
16	3	Grand Total	292,384		R	Address 7	10,552				Address #	9,800	FS
17					6	Total Square Footage FS	117,197				Address #	11,292	FS
18					LO	Address 9	4,792				Address #	16,919	FS
19	Employee 2				1	Total Square Footage LO	4,792				Address 9	4,792	LO
20											Address #	2,700	LO
21					7	Grand Total	121,989				Address #	17,500	LO
22	Bldg #	Address	S/F								Address #	3,091	FS
23	FS	Address 10	54,000								Address #	4,000	FS
24	FS	Address 6	218,384								Address #	7,156	R
25	FS	Address	20,000								Address #	3,115	R
26	3	Total Square Footage FS	292,384								Address #	48,256	FS
27											Address #	3,900	LO
28	3	Grand Total	292,384								Address #	Land	R
Hiatt

Cell Formulas
Range	Formula
N11,N23	N11	=IFERROR(VLOOKUP(Table1[@Address],Table1,3,FALSE),"")
N12:N13,N24:N25	N12	=VLOOKUP([@Address],Table1,3,FALSE)
N14,N26	N14	=COUNTA(N11:N13)
P11:P13,P23:P25	P11	=VLOOKUP([@Address],Table1[[Address]:[Sq ft]],2,FALSE)
P14,P26	P14	=SUM(P11:P13)
N16,N28	N16	=SUM(N14)
P16,P28	P16	=P14
R11:R16,R18	R11	=IFERROR(VLOOKUP([@Address],Table1,3,FALSE),"")
R17	R17	=COUNTA(R11:R16)
R19	R19	=COUNTA(R18)
T11:T16,T18	T11	=IFERROR(VLOOKUP([@Address],Table1,2,FALSE),"")
T17	T17	=SUM(T11:T16)
T19	T19	=SUM(T18)
R21	R21	=SUM(R17+R19)
T21	T21	=SUM(T17,T19)

Cells with Data Validation
Cell	Allow	Criteria
S11:S16	List	=$X$8:$X$107
O11:O13	List	=$X$8:$X$107
S18	List	=$X$8:$X$107
O23:O25	List	=$X$8:$X$107

Okay, so I have a master list of addresses as seen on the right side of the page. My goal is to have the data validation on the address field of each employee (1,2,3) but I do not want the address to be available in the drop down menu if it has been selected for one of the other employees or if I have it selected already. The previous formula worked but it only allowed me to filter it on the 1st employee. Would I have to multiple helper columns and link them to each other? Thank you for your assistance with this!

 

[Peter_SSs]

In my previous layout, change the AB8 formula to this, adding the required table names into the VSTACK function

=FILTER(Table1[Address],ISNA(MATCH(Table1[Address],VSTACK(Table2[Address],Table3[Address],Table4[Address]),0)),"")

All tables would use =$AB$8# in their data validation 'Source' box

 

[ctroxtell]

It works! Can I buy you a beer for the sharing your knowledge? I have one slight issue after using this. After making the selection from the drop-down menu, I get an error stating, "The value in this cell is invalid or missing." I'm guessing this is because it has been removed from the list.

 

[Peter_SSs]

It works! .... After making the selection from the drop-down menu, I get an error

Those seem contradictory!

I am not getting such an error.
Can you detail exactly what you do to get that error?

 

[ctroxtell]

Yes, it works as it only allows me to select the unselected items but in the very left corner of the cells it has the little red triangle. Each drop down is refrenced to $AB$8#, the formula for the AB8 is =FILTER(Table1[Address],ISNA(MATCH(Table1[Address],VSTACK(Table2[Address],Table3[Address],Table4[Address],Table5[Address],Table6[Address],Table7[Address],Table8[Address],Table9[Address],Table10[Address],Table11[Address]),0)),""). Selecting the drop down menu from the list the warning yield sign pops up and states "The value in this cell is invalid or missing. Click on "Display Type Information" for specific details." Clicking "Display Type Information" I get a "Data Type: List, Restriction: Value must match one of the listed items., Blanks will be ignored and are automatically valid."