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]

that csv is not as described
did you save as excel file

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.

 

[pboltonchina]

Yes, it's an .xlxs file. Do you need it in a different format?

 

[etaf]

no -

 

[etaf]

so a bit more complicated , we need to lookup the date and type of meal from meals 1 or meal2 sheet and then lookup the calories on the meal list
BUT - the value in cell C2 on record for a 40g Malt Wheats does not match - record says 43 , and the meal list says 41
also not all the meals in the meal1 are listed in the meals listed
so this may be difficult as structured

 

[pboltonchina]

Sorry, shoud be 41, my mistake

 

[pboltonchina]

Also I am preparing a new list of meals going forward, so ignore the history.

 

[Cubist]

Try this in Record!C7

=XLOOKUP(FILTER('Meals 1'!$B$2:$D$29,'Meals 1'!$A$2:$A$29=Record!B7),'Meals list'!$A$1:$A$38,'Meals list'!$B$1:$B$38)

 

[etaf]

OK, so this is going to be need to be quite precise as it will involve 2 lookups - one as a grid lookup to get the meal and then another to look in a list of meals to get the value

The list of meals needs to be oine long list - not split across a lot of columns
also the name of the meal in the grid has to be exactly the same as the list of meals , any typo will NOT work
also do you intend to keep the 2 meal sheets - why not have all on one
and the header in the meal1 MUST match the same header as in the RECORD

in C2 on the record sheet
copy and paste
=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))

Now you will see a lot of N/A - thats where the meals dont match as mentioned

also not all the meals in the meal1 are listed in the meals listed

quite a few other ways to do this with 365 version - filter etc
anyway , await answeres

 

[pboltonchina]

WOW, Absolutely spot on, I just need to adjust the cell references to do the other columns now.
Thanks for your patience, you've been brilliant

 

[pboltonchina]

I have 2 meal sheets so I have enough room to write information in the boxes and then transfer to my laptop every coupe of days