Removing Data Validation List Items Once Selected In Another Cell

[wryan_garner4]

First, I do have this working, but it seems I am using either too many formulas or the wrong ones, and creating a big delay in my workbook operation.
I have information on three sheets. The first sheet is bulk information in a table. The second sheet is where a lot of my formulas are buried, and mostly placeholders. This sheet will then be hidden once the workbook is functioning correctly. The third sheet is to be used and changed on a regular basis. I have multiple sections with dependent data validation. I want the data validation lists to automatically update and remove an item from the list, if that item is selected in a different cell. What I am finding is that every time I make a change, the entire workbook freezes for a second or two.
Currently on Sheet 2, cell A1002, I have a FILTER formula to pull all items from the bulk information sheet, dependent on what is in cell A1001. That works fine. In cells FZ1002:FZ2002, I have a formula that looks to see if the information in A1002:A2002 is used or not. If it is used, the cell remains blanks. If not used, it shows the row. In MY1001:MY2002, it returns all unused values from A1002:A2002. As I want to use this information in a data validation list without all of the extra blanks, I then have another column TX1002 to FILTER out all of the blanks.
Because of all of these formulas, it is significantly slowing down my workbook.
I want dependent data validation lists, but all used items removed from the list.
How can I remove some of these formulas to speed up my workbook.

Formulas
A1002 =IF(A1001="","",FILTER(IF(CampingGear[Model]="Unknown",CampingGear[Make],CampingGear[Make]&" "& CampingGear[Model]),CampingGear[Description]=A1001,""))
FZ1002 =IF(A1002="","",IF(COUNTIF('Pack Contents'!$D$7:$D$16,A1002),"",ROW()))
MY1002 =LET(FindZero,IF(ROW()-ROW(A$1002)+1>COUNT(FZ$1002:FZ$2002),"",INDEX(A:A,SMALL(FZ$1002:FZ$2002,ROW()-ROW(A$1002)+1))),IF(FindZero=0,"",FindZero))
TX1002 =IF(TX1001="","",IFERROR(FILTER(MY1002:MY2002,MY1002:MY2002<>""),"Item already added. Adjust quantity."))

Camping Gear.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Item #	Make	Model	Description	Type	Quantity	Weight (oz)	Total Weight (oz)		Categories
2	1	MSR	Hubba Hubba	Tent	Shelter	1	68.60	68.60		Pack & Accessories
3	2	MEC	Aquila-7C	Sleeping Bag	Sleeping	1	54.90	54.90		Shelter
4	3	SOG	Shovel	Shovel	Tools	1	25.80	25.80		Sleeping
5	4	Thermarest	ProLite Regular	Sleeping Pad	Sleeping	1	23.00	23.00		Hydration
6	5	MSR	MiniWorks EX Microfilter	Water Filter	Hydration	1	17.00	17.00		Cooking & Eating
7	6	MSR	WhisperLite Universal	Stove	Cooking & Eating	1	16.10	16.10		Toiletries
8	7	SOG	Machete	Machete	Tools	1	15.70	15.70		Navigation
9	8	SOG	Hatchet	Hatchet	Tools	1	15.20	15.20		Clothing
10	9	Sabre	Wild Max	Bear Attack Deterrent	Safety	1	11.00	11.00		Lighting
11	10	Unknown	Unknown	Tent Pegs	Shelter	1	10.80	10.80		Fire
12	11	Jet Boil	Jet Power	Isobutane 8.11oz	Cooking & Eating	2	13.40	26.80		Tools
13	12	Adventure Medical	.9	Medical Kit	First Aid	1	6.20	6.20		Safety
14	13	Camelback	50oz	Water Bladder	Hydration	1	5.80	5.80		First Aid
15	14	Coghlan's	Egg Carrier	Egg Carrier	Cooking & Eating	1	4.50	4.50		Miscellaneous
16	15	MEC	Deluxe Pillow	Pillow	Sleeping	1	4.30	4.30		Optics
17	16	Rapala	Fillet Knife	Filet Knife	Tools	1	4.30	4.30		Test1
18	17	Gorilla	Gorilla Tape	1" x 30' Tape	Miscellaneous	1	4.10	4.10		Test2
19	18	CH Hanson	Flagging Tape	Flagging Tape	Miscellaneous	1	4.10	4.10		Test3
20	19	NRS	35L Mighty Light	Dry Bag	Miscellaneous	1	3.90	3.90
21	20	Havalon	Piranta	Pocket Knife	Tools	1	3.50	3.50
22	21	X-Lite	8862	Utility Lighter	Tools	1	3.00	3.00
23	22	Edgewell	Wet Ones	Hand and Face Wipes	Toiletries	2	2.80	5.60
24	23	Vortex	Lens Cleaning Kit	Lens Cleaning Kit	Miscellaneous	1	2.80	2.80
25	24	John Deere	Toque	Toque	Clothing	1	2.60	2.60
26	25	Unknown	Unknown	Rain Poncho	Clothing	1	2.50	2.50
27	26	Purell	Hand Sanitizer	Hand Sanitizer	Toiletries	1	2.40	2.40
28	27	Unknown	Unknown	Compression Bag	Miscellaneous	1	2.20	2.20
29	28	Colgate	31345	Tooth Brush	Toiletries	1	1.90	1.90
30	29	Grabber	Hand Warmers	Hand Warmers	Miscellaneous	2	1.70	3.40
31	30	Coghlan's	Unknown	Tent Repair Kit	Miscellaneous	1	1.70	1.70
32	31	Old Spice	Swagger	Deodorant	Toiletries	1	1.70	1.70
33	32	Unknown	Unknown	Dish Detergent	Cooking & Eating	1	1.60	1.60
34	33	Garnier	Fructis	Shampoo	Toiletries	1	1.60	1.60
35	34	Old Spice	Swagger	Body Wash	Toiletries	1	1.50	1.50
36	35	Unknown	Unknown	Paracord (3ft.)	Miscellaneous	1	1.40	1.40
37	36	Silva	426	Compass	Navigation	1	1.30	1.30
38	37	Coghlan's	Blue Bells	Bell	Safety	2	1.20	2.40
39	38	Ozark Trail	Mini 20 lumens	Headlamp	Lighting	1	1.00	1.00
40	39	Coghlan's	Tissue On The Go	Toilet Paper	Toiletries	1	0.90	0.90
41	40	Unknown	Unknown	Dish Cloth	Cooking & Eating	2	0.90	1.80
42	41	Mastercraft	Clamp	Clamp	Miscellaneous	1	0.80	0.80
43	42	After Bite	The Itch Eraser	After Bite	First Aid	1	0.80	0.80
44	43	Unknown	Unknown	Floating Tea Light	Miscellaneous	5	0.60	3.00
45	44	SOL	Fire Lite	Fire Starter	Miscellaneous	23	0.54	12.42
46	45	Tylenol	Extra Strength	Acetaminophen	First Aid	1	0.50	0.50
47	46	MSR	Tent Pole Splice	Tent Pole Splice	Miscellaneous	1	0.50	0.50
48	47	Unknown	Unknown	Carabiner	Miscellaneous	2	0.40	0.80
49	48	Blistex	Regular	Lip Balm	Toiletries	1	0.30	0.30
50	49	GSI	Fork	Fork	Cooking & Eating	2	0.40	0.80
51	50	GSI	Knife	Knife	Cooking & Eating	2	0.40	0.80
52	51	GSI	Spoon	Spoon	Cooking & Eating	2	0.40	0.80
53	52	SOG	Hunting Knife	Knife	Tools	1	7.40	7.40
54	53	TruFlare	O2C	Pen Flare	Safety	1	1.40	1.40
55	54	TruFlare	15mm Exploding Cartridge	Bear Banger Cartridge	Safety	2	0.40	0.80
56	55	TruFlare	15mm Signal Flare	Signal Flare	Safety	3	0.30	0.90
57	56	Uco	Storm Matches	Stormproof Matches	Fire	1	1.70	1.70
58	57	MSR	Trail Lite	2.0L Aluminum Pot	Cooking & Eating	1	8.70	8.70
59	58	MSR	Trail Lite	0.7L Plastic Bowl	Cooking & Eating	2	2.20	4.40
60	59	Outbound	Stainless Steel Mug	Mug 532ml	Cooking & Eating	1	5.60	5.60
61	60	Mead	Memo	Notepad	Miscellaneous	2	1.60	3.20
62	61	Unknown	Unknown	Stormproof Matches	Fire	1	0.90	0.90
63	62	Nalgene	30ml Container	30ml Storage Container	Miscellaneous	0	1.20	0.00
64	63	MSR	Trail Lite	Insulated Mug	Cooking & Eating	2	4.40	8.80
65	64	Gerber	Vital Pack Saw	Bone Saw	Tools	1	2.70	2.70
66	65	Hot Shot	Thinsulate 40 Gram Gloves	Gloves	Clothing	1	2.00	2.00
67	66	Vortex	Binocular Harness	Binocular Harness	Optics	1	3.50	3.50
68	67	Vortex	Glasspak	Binocular Carrier	Optics	1	7.90	7.90
69	68	Vortex	Diamondback 10x50	Binoculars	Optics	1	2.00	2.00
70	69	Cuisinart	Fry Pan	Fry Pan 5.5"	Cooking & Eating	1	5.50	5.50
71	70	Master Chef	Mixing Spoon	Mixing Spoon	Cooking & Eating	1	2.90	2.90
72	71	Jet Boil	Collapsible Fork	Fork	Cooking & Eating	1	0.50	0.50
73	72	Jet Boil	Collapsible Spoon	Spoon	Cooking & Eating	1	0.50	0.50
74	73	Starfrit	Can Opener	Can Opener	Cooking & Eating	1	4.10	4.10
75	74	Garmin	64s	GPS	Navigation	1	8.10	8.10
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
Bulk Gear List

Cell Formulas
Range	Formula
J21	J21	=FILTER(CampingGear[Description],CampingGear[Type]=G5,"")
A2:A75	A2	=IF(B2<>"",IF(A1="",1,ROW()-1),"")
H2:H96	H2	=IF(G2="","",IF(F2="","",F2*G2))

Named Ranges
Name	Refers To	Cells
'Bulk Gear List'!_FilterDatabase	='Bulk Gear List'!$A$1:$H$100	A2
Description	=CampingGear[Description]	J21
Type	=CampingGear[Type]	J21

Cells with Data Validation
Cell	Allow	Criteria
E2:E100	List	=Categories1

Camping Gear.xlsx
	A
1000	1
1001	Backpack
1002
1003
1004
1005
1006
Sheet1

Cell Formulas
Range	Formula
A1001	A1001	=IF(INDIRECT("'Pack Contents'!C"&(A1000+6))="","",INDIRECT("'Pack Contents'!C"&A1000+6))
A1002	A1002	=IF(A1001="","",FILTER(IF(CampingGear[Model]="Unknown",CampingGear[Make],CampingGear[Make]&" "& CampingGear[Model]),CampingGear[Description]=A1001,""))

Named Ranges
Name	Refers To	Cells
Description	=CampingGear[Description]	A1002

Camping Gear.xlsx
	FZ
1000	1
1001	Backpack
1002
1003
1004
1005
1006
1007
1008
Sheet1

Cell Formulas
Range	Formula
FZ1001	FZ1001	=A1001
FZ1002:FZ1008	FZ1002	=IF(A1002="","",IF(COUNTIF('Pack Contents'!$D$7:$D$16,A1002),"",ROW()))

Camping Gear.xlsx
	MY
1000	1
1001	Backpack
1002
1003
1004
1005
1006
1007
1008
1009
Sheet1

Cell Formulas
Range	Formula
MY1001	MY1001	=A1001
MY1002:MY1009	MY1002	=LET(FindZero,IF(ROW()-ROW(A$1002)+1>COUNT(FZ$1002:FZ$2002),"",INDEX(A:A,SMALL(FZ$1002:FZ$2002,ROW()-ROW(A$1002)+1))),IF(FindZero=0,"",FindZero))

Camping Gear.xlsx
	TX
1000	1
1001	Backpack
1002	Item already added. Adjust quantity.
1003
1004
1005
1006
1007
1008
Sheet1

Cell Formulas
Range	Formula
TX1001	TX1001	=A1001
TX1002	TX1002	=IF(TX1001="","",IFERROR(FILTER(MY1002:MY2002,MY1002:MY2002<>""),"Item already added. Adjust quantity."))

 

[James006]

Hi,

If you need to hide Used Items from your Data Validation, see this method from Contextures

Excel Data Validation Drop Down List-Hide Used Items

In Excel data validation drop down list hide previously selected items, to avoid duplicate entries. Video shows steps. Sample Excel file

www.contextures.com

 

[wryan_garner4]

Hi,

If you need to hide Used Items from your Data Validation, see this method from Contextures

Excel Data Validation Drop Down List-Hide Used Items

In Excel data validation drop down list hide previously selected items, to avoid duplicate entries. Video shows steps. Sample Excel file

www.contextures.com

Thanks James006! This cleared up a lot of formulas for me and made the workbook a lot faster. Greatly appreciated.

 

[James006]

Very glad to hear you have managed to fix your problem