How to convert VLOOKUP values to text on another sheet AUTOMATICALLY

[Lady18]

This is what my workbook looks like. I want a formula(s) that will take all this data, which is subject to change, and automatically put it in a nice, neat list on the "Items List" page. How can I achieve this?

 

[oldbrewer]

do you want every recipe in column A on a new sheet - or just your selected recipe selected by today's date

 

[Lady18]

I want every recipe in every column on a new sheet. You see, I have to plan 2 weeks worth of supper meals for the family I live with. At the beginning of this two week period, I need to check the inventory, so to speak. So I want a list that displays all the ingredients, including duplicates. I got this far

 

[Lady18]

 

[oldbrewer]

[TABLE="******* 1152"]
<colgroup><col width="64" span="18" style="*******48pt"> </colgroup><tbody>[TR]
[TD="******* 64"][/TD]
[TD="******* 64"]rec1[/TD]
[TD="******* 64"]rec2[/TD]
[TD="******* 64"]rec3[/TD]
[TD="******* 64"]rec4[/TD]
[TD="******* 64"]rec5[/TD]
[TD="******* 64"]rec6[/TD]
[TD="******* 64"]rec7[/TD]
[TD="******* 64"]rec8[/TD]
[TD="******* 64"]rec9[/TD]
[TD="******* 64"]rec10[/TD]
[TD="******* 64"][/TD]
[TD="******* 64"][/TD]
[TD="******* 64"][/TD]
[TD="******* 64"][/TD]
[TD="******* 64"][/TD]
[TD="******* 64"][/TD]
[TD="******* 64"][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]r1ingr1[/TD]
[TD]r2ingr1[/TD]
[TD]r3ingr1[/TD]
[TD]r4ingr1[/TD]
[TD]r5ingr1[/TD]
[TD]r6ingr1[/TD]
[TD]r7ingr1[/TD]
[TD]r8ingr1[/TD]
[TD]r9ingr1[/TD]
[TD]r10ingr1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]r1ingr2[/TD]
[TD]r2ingr2[/TD]
[TD]r3ingr2[/TD]
[TD]r4ingr2[/TD]
[TD]r5ingr2[/TD]
[TD]r6ingr2[/TD]
[TD]r7ingr2[/TD]
[TD]r8ingr2[/TD]
[TD]r9ingr2[/TD]
[TD]r10ingr2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]r1ingr3[/TD]
[TD]r2ingr3[/TD]
[TD]r3ingr3[/TD]
[TD]r4ingr3[/TD]
[TD]r5ingr3[/TD]
[TD]r6ingr3[/TD]
[TD]r7ingr3[/TD]
[TD]r8ingr3[/TD]
[TD]r9ingr3[/TD]
[TD]r10ingr3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]r1ingr4[/TD]
[TD]r2ingr4[/TD]
[TD]r3ingr4[/TD]
[TD]r4ingr4[/TD]
[TD]r5ingr4[/TD]
[TD]r6ingr4[/TD]
[TD]r7ingr4[/TD]
[TD]r8ingr4[/TD]
[TD]r9ingr4[/TD]
[TD]r10ingr4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]r1ingr5[/TD]
[TD]r2ingr5[/TD]
[TD]r3ingr5[/TD]
[TD]r4ingr5[/TD]
[TD]r5ingr5[/TD]
[TD]r6ingr5[/TD]
[TD]r7ingr5[/TD]
[TD]r8ingr5[/TD]
[TD]r9ingr5[/TD]
[TD]r10ingr5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]rec11[/TD]
[TD]rec12[/TD]
[TD]rec13[/TD]
[TD]rec14[/TD]
[TD]rec15[/TD]
[TD]rec16[/TD]
[TD]rec17[/TD]
[TD]rec18[/TD]
[TD]rec19[/TD]
[TD]rec20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]r11ingr1[/TD]
[TD]r12ingr1[/TD]
[TD]r13ingr1[/TD]
[TD]r14ingr1[/TD]
[TD]r15ingr1[/TD]
[TD]r16ingr1[/TD]
[TD]r17ingr1[/TD]
[TD]r18ingr1[/TD]
[TD]r19ingr1[/TD]
[TD]r20ingr1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]r11ingr2[/TD]
[TD]r12ingr2[/TD]
[TD]r13ingr2[/TD]
[TD]r14ingr2[/TD]
[TD]r15ingr2[/TD]
[TD]r16ingr2[/TD]
[TD]r17ingr2[/TD]
[TD]r18ingr2[/TD]
[TD]r19ingr2[/TD]
[TD]r20ingr2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]r11ingr3[/TD]
[TD]r12ingr3[/TD]
[TD]r13ingr3[/TD]
[TD]r14ingr3[/TD]
[TD]r15ingr3[/TD]
[TD]r16ingr3[/TD]
[TD]r17ingr3[/TD]
[TD]r18ingr3[/TD]
[TD]r19ingr3[/TD]
[TD]r20ingr3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]r11ingr4[/TD]
[TD]r12ingr4[/TD]
[TD]r13ingr4[/TD]
[TD]r14ingr4[/TD]
[TD]r15ingr4[/TD]
[TD]r16ingr4[/TD]
[TD]r17ingr4[/TD]
[TD]r18ingr4[/TD]
[TD]r19ingr4[/TD]
[TD]r20ingr4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]r11ingr5[/TD]
[TD]r12ingr5[/TD]
[TD]r13ingr5[/TD]
[TD]r14ingr5[/TD]
[TD]r15ingr5[/TD]
[TD]r16ingr5[/TD]
[TD]r17ingr5[/TD]
[TD]r18ingr5[/TD]
[TD]r19ingr5[/TD]
[TD]r20ingr5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]problem statement[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]choose any recipe and display it[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]rec15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]r15ingr1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]r15ingr2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD]r15ingr3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD]r15ingr4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD]r15ingr5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]formula giving first ingredient[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 16"]=IF(ISERROR(OFFSET($A$1,B25,MATCH($C$23,$B$1:$K$1,0))),OFFSET($A$9,B25,MATCH($C$23,$B$9:$K$9,0)),OFFSET($A$1,B25,MATCH($C$23,$B$1:$K$1,0)))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Lady18]

Thanks so much for your time oldbrewer! It'll take me a while to digest this, but I am most grateful for your consideration

 

[oldbrewer]

digest it.......boom boom

 

[Lady18]

oldbrewer, I think I'm past the part about "choose any recipe and display it". What I want to do is take all the ingredients for up to 35 recipes, and condense them into a list like this

I uploaded a near copy of this workbook to my Google Drive if it helps any - http://docs.google.com/spreadsheets/d/1cKemVkQxAbHup6DRkVseH6CLLPW5spTzwu3Q2jZSzek/edit?usp=sharing - it may take a second or two for the sheet tabs to load.

 

[oldbrewer]

my post 5 lists ingredients for all meals so choose which 14 and list the ingredients seems too easy - am I missing something ?

 

[Lady18]

Maybe I'm too ignorant about Excel, so I'm probably the one who's missing something. Let me start from the very beginning.<o></o>
<o></o>
I’m thinking “What supper meals can I cook thatthey like and still offer variety?” First I need to know what dishes they like, then try to get 14 days between cooking that same meal. (Tonight we are having hamburger patties, salad, and baked potatoes.)

<o></o>
Thisis the first sheet – the “Menu” sheet. The only formulas in it are in columns H and J, =TODAY() and =IF(I4="","",H4-I4)respectively.

Then I started wishing for an easier way to see/findthe ingredients for the stuff I planned to cook – easier than literally openingthe drawer on my file cabinet, finding the “Recipes” folder, digging for theright recipes, and typing all that stuff on a word document. So I googled something along the lines of ‘grocerylist’, or ‘recipe ingredients’, etc., for Excel, and came up with thisExcelIsFun video - https://www.youtube.com/watch?v=BwPog6olWpc . I didn’t know what VLOOKUP was beforewatching this video. And I thought, “Yes! That’s what I want!”<o></o>

Hencethe “Recipe Ingredients” sheet – the second sheet