Data Validation reference selection reference other worksheet

jjp2985

New Member
Joined
Aug 13, 2021
Messages
12
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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
ABCDEFGHIJKLM
15Pack Type1 single pack has 100 caps in it1 Large bag has 10 single pack of caps in it
16Large BagDateInitialsDepartmentCap TypePack TypeNumber of Pack(s)/Cap(s) Took/ReceivedAmount Small Packs or Weight TookRemaning 2mL Prep Vial Black Cap Small Packs in InventoryRemaning GC Vial Cap Small Packs in InventoryRemaning 4mL Glass Vial Cap Small Packs in InventoryRemaning Test Tube Cap Small Packs in InventoryRemaning LC Vial Cap Small Packs in Inventory
17Single Pack12-Mar-2021JJPInventoryGC Vial Cap153615361823115812351486
18Microcentrifuge Cap Bag23-Feb-2021JJPCommercial2mL Prep Vial Black CapLarge Bag55014861823115812351486
1921-Apr-2021Commercial2mL Prep Vial Black CapLarge Bag1111013761823115812351486
20Cap Type12-Feb-2021R&D2mL Prep Vial Black CapLarge Bag2323011461823115812351486
212mL Prep Vial Black Cap11-Jan-2021UKGC Vial CapLarge Bag4242011461403115812351486
22GC Vial Cap21-Feb-2021Australia4mL Glass Vial CapLarge Bag323201146140383812351486
234mL Glass Vial Cap2-Apr-2021InventoryGC Vial CapLarge Bag20020001146340383812351486
24Test Tube Cap3-May-2021Commercial2mL Prep Vial Black CapLarge Bag7701076340383812351486
25LC Vial Cap2-Jun-2021Research4mL Glass Vial CapLarge Bag111101076340372812351486
262mL Prep Vial Green Cap5-Apr-2021R&D2mL Prep Vial Black CapLarge Bag23230846340372812351486
27Microcentrifuge Tube Red Caps5-Apr-2021UKGC Vial CapLarge Bag42420846298372812351486
28Microcentrifuge Tube Yellow Caps4-May-2021Australia4mL Glass Vial CapLarge Bag32320846298340812351486
29Microcentrifuge Tube Purple Caps3-Jul-2021InventoryGC Vial CapLarge Bag2002000846498340812351486
30Microcentrifuge Tube Orange Caps5-Jul-2021Commercial2mL Prep Vial Black CapLarge Bag13130716498340812351486
31Microcentrifuge Tube Clear Caps3-Aug-2021Research4mL Glass Vial CapLarge Bag11110716498329812351486
324-Sep-2021R&D2mL Prep Vial Black CapLarge Bag23230486498329812351486
335-Sep-2021UKGC Vial CapLarge Bag42420486456329812351486
342-Aug-2021Australia4mL Glass Vial CapLarge Bag323204864563-2212351486
Sheet1
Cell Formulas
RangeFormula
I16:M16I16=CONCATENATE("Remaning ",INDEX(Cap_Type,COLUMN()-8)," Small Packs in Inventory")
H18:H34H18=IF(F18=$A$16,G18*10,IF(F18=$A$18,G18*500,IF(F18=""," ",G18)))
I18:M34I18=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
CellConditionCell FormatStop If True
I17:K34,L18:M34Cell Value<200textNO
I17:K34,L18:M34Cell Value<500textNO
Cells with Data Validation
CellAllowCriteria
D17:D34List=$A$6:$A$12
E17:E34List=$A$21:$A$31
F17List=$A$16:$A$18
F18:F22List=$A$16:$A$18
F23List=$A$16:$A$18
F24:F28List=$A$16:$A$18
F29List=$A$16:$A$18
F30:F34List=$A$16:$A$18
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,974
Office Version
  1. 365
Platform
  1. Windows
Create a named range for the data in the table & then use that in the data validation.
 

jjp2985

New Member
Joined
Aug 13, 2021
Messages
12
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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.

1629047730016.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,974
Office Version
  1. 365
Platform
  1. Windows
You cannot use the name of the table, you need to create a named range & use that.
1629048084832.png
 
Solution

jjp2985

New Member
Joined
Aug 13, 2021
Messages
12
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
Ok thanks for the clarification on that and it worked out great. Thanks for the help.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,974
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,144,340
Messages
5,723,804
Members
422,518
Latest member
quack_quack

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top