Link cells

[pboltonchina]

Hi Everyone,
Been a long time since I did any of this, but here goes.
I have a sheet with a list of meals, at the side of the meal list is a column with the Carbs value. So Sheet "Meals List" has the data for the drop down, sheet "Meals 1" has the drop down itself and sheet "Record" is where I want the copied value pasting. When I select from the drop down list I want to put the carbs value for that item in a cell on the Records sheet. I hope this makes sense to someone who could help me with this.

As always, many thanks in advance for any help offered.
Regards
Paul

 

[etaf]

not sure who you are responding to
maybe , as posted by CUBIST using filter and lookup
=XLOOKUP(FILTER('Meals 1'!$B$2:$D$1000,'Meals 1'!$A$2:$A$1000=record!B2),'Meals list'!$A$1:$A$1000,'Meals list'!$B$1:$B$1000)
and for meals 2
=XLOOKUP(FILTER('Meals 2'!$B$2:$D$1000,'Meals 2'!$A$2:$A$1000=record!B2),'Meals list'!$A$1:$A$1000,'Meals list'!$B$1:$B$1000)

may need to extend the ranges a lot further

Dropbox

www.dropbox.com

only on dropbox for a few days

June Carb Counter - ETAF.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1		Date	Breakfast	Lunch	Dinner	Dessert	Supper	Snacks	Total	Allowance	Difference
2		3-Jun	41.0	1234.0	#N/A	#N/A		#N/A		170.0	170.0
3		4-Jun	62.3	#N/A	#N/A	4567.0		#N/A		170.0	170.0
4		5-Jun	41.0	#N/A	#N/A	#N/A				170.0	170.0
5		6-Jun	41.0	82.0			#N/A	#N/A		170.0	170.0
6		7-Jun	41.0	#N/A	#N/A	4567.0		#N/A		170.0	170.0
7		8-Jun	41.0							170.0	170.0
8		9-Jun								170.0	170.0
9		10-Jun								170.0	170.0
10		11-Jun								170.0	170.0
11		12-Jun								170.0	170.0
12		13-Jun								170.0	170.0
13		14-Jun								170.0	170.0
14		15-Jun								170.0	170.0
15		16-Jun								170.0	170.0
16		17-Jun								170.0	170.0
17		18-Jun								170.0	170.0
18		19-Jun								170.0	170.0
19		20-Jun								170.0	170.0
20		21-Jun								170.0	170.0
21		22-Jun								170.0	170.0
22		23-Jun								170.0	170.0
23		24-Jun								170.0	170.0
24		25-Jun								170.0	170.0
25		26-Jun								170.0	170.0
26		27-Jun								170.0	170.0
27		28-Jun								170.0	170.0
28		29-Jun								170.0	170.0
29		30-Jun								170.0	170.0
30
record

Cell Formulas
Range	Formula
C2:E29	C2	=XLOOKUP(FILTER('Meals 1'!$B$2:$D$1000,'Meals 1'!$A$2:$A$1000=record!B2),'Meals list'!$A$1:$A$1000,'Meals list'!$B$1:$B$1000)
F2:H29	F2	=XLOOKUP(FILTER('Meals 2'!$B$2:$D$1000,'Meals 2'!$A$2:$A$1000=record!B2),'Meals list'!$A$1:$A$1000,'Meals list'!$B$1:$B$1000)
K2:K29	K2	=SUM(J2-I2)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K2:K29	Cell Value	>0	text	NO
K2:K29	Cell Value	<0	text	NO
K2:K29	Cell Value	>43.4	text	NO

June Carb Counter - ETAF.xlsx
	A	B	C	D
1	Date	Breakfast	Lunch	Dinner
2	3-Jun	40g Malt Wheats	Ham Salad	Egg On Beans On Toast
3	4-Jun	60g Malt Wheats	Chicken Soup	Cheese Omelette + Sausage + Peppers
4	5-Jun	40g Malt Wheats	Turkey Sandwich And Crisps	Scampi, Fried Potatoes & Peas
5	6-Jun	40g Malt Wheats	Chip Butty
6	7-Jun	40g Malt Wheats	Fried Eggs On Toast	Chicken Stir Fry With Noodles
7	8-Jun	40g Malt Wheats
8	9-Jun
9	10-Jun
10	11-Jun
Meals 1

Cells with Data Validation
Cell	Allow	Criteria
B8:B10	List	='Meals list'!$A$1:$A$3
C7:C29	List	='Meals list'!$A$5:$A$15
D7:D29	List	='Meals list'!$A$17:$A$32

June Carb Counter - ETAF.xlsx
	A	B
1	60g Malt wheats	62.3
2	40g Malt Wheats	41
3	2 Weetabix	42.6
4
5	Chicken & Mushroom	18.6
6	Tomato & Basil	26.2
7	Beef & veg Big Soup	15
8	Salad with H & M Dressing	8
9	Salad with Caesar Dressing	20
10	Salad with Vinaigrette Dressing	11.6
11	Beans On Toast	34
12	Egg On Toast	18.5
13	Chip Butty	82
14	2 Crumpets	40
15	Sandwich (any)	37
16
17	Risotto	60
18	Fry Up	0
19	Pork Schnitzel	82
20	Bacon Wrapped Chicken	42
21	Omelette	3
22	Spag Bol	78
23	Chicken Burger & Onion Rings	23
24	Chicken Korma & Rice	62
25	Fish & Chips	76
26	Fish , Chips & Mushy Peas	91
27	Shepherds Pie	20
28	Gnocchi	65
29	Stir Fry	20
30	Pie & Peas	55
31	Salmon	52
32	Steak	42
33
34	Jelly	18
35	Jelly & Raspberries	23
36	Jelly & Strawberries	24
37	Lemon Yoghurt	9
38	Mango, Papaya & Passion Fruit	19.1
39	Ham Salad	1234
40	Peach, Passion Fruit & Mango Yoghurt	4567
Meals list

 

[pboltonchina]

Hi etaf,
Seems to be working fine, is there any way of getting rid of al the #N/A's populating cells. I don't understand the formula so I don't know why some cells are clear and others showing #N/A?
Cheers
Paul

 

[etaf]

N/A means an error and could not find the lookup value
you can use an IFERROR ( ) if using the index/match version
OR xlookup has an error condition

=XLOOKUP(FILTER('Meals 1'!$B$2:$D$1000,'Meals 1'!$A$2:$A$1000=record!B2),'Meals list'!$A$1:$A$1000,'Meals list'!$B$1:$B$1000,"")
=XLOOKUP(FILTER('Meals 2'!$B$2:$D$1000,'Meals 2'!$A$2:$A$1000=record!B2),'Meals list'!$A$1:$A$1000,'Meals list'!$B$1:$B$1000,"")

Dropbox

www.dropbox.com

for this version add an IFERROR to this
=INDEX('Meals list'!$B$1:$B$1000,MATCH(INDEX('Meals 1'!$B$2:$D$100,MATCH(Record!$B2,'Meals 1'!$A$2:$A$100,0),MATCH(Record!C$1,'Meals 1'!$B$1:$D$1,0)),'Meals list'!$A$1:$A$1000,0))

=iferror(INDEX('Meals list'!$B$1:$B$1000,MATCH(INDEX('Meals 1'!$B$2:$D$100,MATCH(Record!$B2,'Meals 1'!$A$2:$A$100,0),MATCH(Record!C$1,'Meals 1'!$B$1:$D$1,0)),'Meals list'!$A$1:$A$1000,0)),"")

the filter version
FILTER('Meals 1'!$B$2:$D$1000,'Meals 1'!$A$2:$A$1000=record!B2),
returns ALL the Meals which match the data - as meals 1 headers are in the same order as the record then they match
and so that will return - the meals for that date for the header array

and then xlookup uses the value found in the meals 1 & meals 2 sheets and then returns the value in the list column B which matches those meals in column A

 

[pboltonchina]

Thanks, all sorted now and working a treat. Once again, thank you

 

[etaf]

welcome