Data Validation reference selection reference other worksheet

[jjp2985]

I am utilizing Data Validation but am having difficulties with reference range updating. I put my list into a Table so when I need to add new things to the list the Table will expand. For the attached example cells E17:E34 has Data Validation and for reference range it is using A21:A31 which is a Table. If I add a new item to that Table List then the Table expands and so does the Data Validation reference range so it all works great. However, the trouble comes if I have the Table on another worksheet and then try and utilize the the reference range for the Table on another worksheet. If I were to add another item to the Table then the Table updates correctly however, the Data Validation reference range does not. How would I be able to get this to work utilizing a Table on another worksheet?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
15	Pack Type	1 single pack has 100 caps in it						1 Large bag has 10 single pack of caps in it
16	Large Bag	Date	Initials	Department	Cap Type	Pack Type	Number of Pack(s)/Cap(s) Took/Received	Amount Small Packs or Weight Took	Remaning 2mL Prep Vial Black Cap Small Packs in Inventory	Remaning GC Vial Cap Small Packs in Inventory	Remaning 4mL Glass Vial Cap Small Packs in Inventory	Remaning Test Tube Cap Small Packs in Inventory	Remaning LC Vial Cap Small Packs in Inventory
17	Single Pack	12-Mar-2021	JJP	Inventory	GC Vial Cap			1536	1536	1823	1158	1235	1486
18	Microcentrifuge Cap Bag	23-Feb-2021	JJP	Commercial	2mL Prep Vial Black Cap	Large Bag	5	50	1486	1823	1158	1235	1486
19		21-Apr-2021		Commercial	2mL Prep Vial Black Cap	Large Bag	11	110	1376	1823	1158	1235	1486
20	Cap Type	12-Feb-2021		R&D	2mL Prep Vial Black Cap	Large Bag	23	230	1146	1823	1158	1235	1486
21	2mL Prep Vial Black Cap	11-Jan-2021		UK	GC Vial Cap	Large Bag	42	420	1146	1403	1158	1235	1486
22	GC Vial Cap	21-Feb-2021		Australia	4mL Glass Vial Cap	Large Bag	32	320	1146	1403	838	1235	1486
23	4mL Glass Vial Cap	2-Apr-2021		Inventory	GC Vial Cap	Large Bag	200	2000	1146	3403	838	1235	1486
24	Test Tube Cap	3-May-2021		Commercial	2mL Prep Vial Black Cap	Large Bag	7	70	1076	3403	838	1235	1486
25	LC Vial Cap	2-Jun-2021		Research	4mL Glass Vial Cap	Large Bag	11	110	1076	3403	728	1235	1486
26	2mL Prep Vial Green Cap	5-Apr-2021		R&D	2mL Prep Vial Black Cap	Large Bag	23	230	846	3403	728	1235	1486
27	Microcentrifuge Tube Red Caps	5-Apr-2021		UK	GC Vial Cap	Large Bag	42	420	846	2983	728	1235	1486
28	Microcentrifuge Tube Yellow Caps	4-May-2021		Australia	4mL Glass Vial Cap	Large Bag	32	320	846	2983	408	1235	1486
29	Microcentrifuge Tube Purple Caps	3-Jul-2021		Inventory	GC Vial Cap	Large Bag	200	2000	846	4983	408	1235	1486
30	Microcentrifuge Tube Orange Caps	5-Jul-2021		Commercial	2mL Prep Vial Black Cap	Large Bag	13	130	716	4983	408	1235	1486
31	Microcentrifuge Tube Clear Caps	3-Aug-2021		Research	4mL Glass Vial Cap	Large Bag	11	110	716	4983	298	1235	1486
32		4-Sep-2021		R&D	2mL Prep Vial Black Cap	Large Bag	23	230	486	4983	298	1235	1486
33		5-Sep-2021		UK	GC Vial Cap	Large Bag	42	420	486	4563	298	1235	1486
34		2-Aug-2021		Australia	4mL Glass Vial Cap	Large Bag	32	320	486	4563	-22	1235	1486
Sheet1

Cell Formulas
Range	Formula
I16:M16	I16	=CONCATENATE("Remaning ",INDEX(Cap_Type,COLUMN()-8)," Small Packs in Inventory")
H18:H34	H18	=IF(F18=$A$16,G18*10,IF(F18=$A$18,G18*500,IF(F18=""," ",G18)))
I18:M34	I18	=IF(AND($D18="Inventory",$E18=INDEX(Cap_Type,COLUMNS($I:I))),I17+$H18,IF(OR($D18="",$E18=""),"",IF($E18=INDEX(Cap_Type,COLUMNS($I:I)),I17-$H18,I17)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I17:K34,L18:M34	Cell Value	<200	text	NO
I17:K34,L18:M34	Cell Value	<500	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D17:D34	List	=$A$6:$A$12
E17:E34	List	=$A$21:$A$31
F17	List	=$A$16:$A$18
F18:F22	List	=$A$16:$A$18
F23	List	=$A$16:$A$18
F24:F28	List	=$A$16:$A$18
F29	List	=$A$16:$A$18
F30:F34	List	=$A$16:$A$18

 

[Fluff]

Create a named range for the data in the table & then use that in the data validation.

 

[jjp2985]

Hi, I tried that and encounter an error with that. I pasted a picture of what I get. The table name is Cap_Type and if I were to put just Cap_Type in the reference range without the = sign then it will only list Cap_Type as an option to choose from.

I created the Table by highlighting the range I had and pressing Crtl + T not sure if that matters in this instance.

 

[Fluff]

You cannot use the name of the table, you need to create a named range & use that.

 

[jjp2985]

Ok thanks for the clarification on that and it worked out great. Thanks for the help.

 

[Fluff]

You're welcome & thanks for the feedback.