Data Validation with named range?

[welshgasman]

Hi all,
I have created an excel workbook, to keep track of the carbs I eat, as I have just been diagnosed with Type 2 diabetes. This is called Foods. I created a dynamic range and named it Item as the foods will grow as I find others that are low carb.
So I have a sheet for the foods I eat, with their carbs, sugar and net carbs plaus the date.
On another sheet I have the foods I actually eat per day, picking from the list on the Foods sheet.

However I have discovered that if I use the named range, then entering an invalid entry just gets accepted. I just changed the validation to Foods!$A2:$A1000 and an invalid entry gets rejected with the normal error message.

This is the first time I have used a dynamic range for data validation. Is there a way to get the same error validation as a hardcoded range at all please?

Diabetes.xlsx
	A	B	C	D	E	F
1	Date	Meal	Item	Factor	Carbs	Sugar
2	14/04/2023	Lunch	Tortilla, DK	1	14.60	0.80
3	14/04/2023	Lunch	Lettuce	0.26	0.36	0.36
4	14/04/2023	Lunch	Celery,Fruit & Nut Sald	0.91	8.92	7.55
5	14/04/2023	Lunch	Southern Fried Chicken, Tesco	4	45.20	1.20
6	14/04/2023	Dinner	Southern Fried Chicken, Tesco	4	45.20	1.20
7	14/04/2023	Snacks	Ice cream Carte Dor Mint 100g	1.3	36.40	31.20
8	15/04/2023	Breakfast	Bacon	2	0.20	0.00
9	15/04/2023	Breakfast	Pork Sausage	2	1.60	1.20
10	15/04/2023	Breakfast	Egg	2	1.20	1.20
11	15/04/2023	Dinner	Lettuce	0.39	0.55	0.55
12	15/04/2023	Dinner	Spring Onions	0.14	1.01	0.45
13	15/04/2023	Dinner	Cucumber	0.28	1.06	0.48
14	15/04/2023	Dinner	Coleslaw Aldi	0.47	3.15	2.54
15	15/04/2023	Snacks	Kit Kat Easter Egg	0.6	54.72	54.24
16	15/04/2023	Drink	Abbot Ale	6	90.00	90.00
17	16/04/2023	Lunch	Tortilla, DK	1	14.60	0.80
18	16/04/2023	Lunch	Lettuce	0.29	0.41	0.41
19	16/04/2023	Lunch	Chicken & Bacon Salad	1.06	3.82	0.53
20	16/04/2023	Dinner	Potatoes	2.7	41.58	9.45
21	16/04/2023	Dinner	Mushrooms	0.5	0.20	0.10
22	16/04/2023	Drink	Rose Wine (Grove Manor) 100ml	15	45.00	45.00
23	16/04/2023	Snacks	Chocolate Chip (1)	1	7.00	3.60
24	17/04/2023	Breakfast	Bluberries	0.42	3.82	3.82
25	17/04/2023	Breakfast	Yoghurt Aldi	1.33	5.72	4.52
26	17/04/2023	Snacks	Chocolate Chip (1)	3	21.00	10.80
27					0.00	0.00
Daily

Cell Formulas
Range	Formula
E2:E27	E2	=IF($D2="",0,VLOOKUP($C2,Foods,2,FALSE)*$D2)
F2:F27	F2	=IF($D2="",0,VLOOKUP($C2,Foods,3,FALSE)*$D2)

Cells with Data Validation
Cell	Allow	Criteria
B:B	List	=Meals
C:C	List	=Item

 

[RoryA]

Make sure you uncheck the 'Ignore blank' option in the DV settings.

 

[welshgasman]

I thought I had posted a reply to this? Sorry.
That worked @RoryA

Thank you.