Arrays/vlookup???
 

Hi I am very much an amateur with excel I only really use it to build product
sheets and recipe templates. I am basically trying to do this and I can't
seem to figure it out ( I don't speak excel too well)....

I have a recipe card on sheet 2
In cell A10 I would type in an ingredient "flour"
and what I would like it to do is put my cost per/g in cell g10 which is
linked to my master price list
here is the catch I have over 300+ recipes and I don't want to manually link
all of my G cells with the master price list
I would like to copy and paste my recipes into my current template and link
corresponding words with the appropriate cell on a different sheet. Is this
possible?

Arrays/vlookup???
 

Hi,

You could use the VLOOKUP() function here. Please read up on the same in
the Help menu.
--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Ian" wrote in message
...
Hi I am very much an amateur with excel I only really use it to build
product
sheets and recipe templates. I am basically trying to do this and I can't
seem to figure it out ( I don't speak excel too well)....

I have a recipe card on sheet 2
In cell A10 I would type in an ingredient "flour"
and what I would like it to do is put my cost per/g in cell g10 which is
linked to my master price list
here is the catch I have over 300+ recipes and I don't want to manually
link
all of my G cells with the master price list
I would like to copy and paste my recipes into my current template and
link
corresponding words with the appropriate cell on a different sheet. Is
this
possible?

Arrays/vlookup???
 

OK you want to type in the indgredent in one cell and then the
cost pe grams will auto popluate in another cell.
OK on sheet two column A list you indg. in column list the cost per gram.

ok so in sheet
lets say col a is the ingredent and colm b is the grams.
in column b type
=IF(ISNA(VLOOKUP(a1,SHEET2!$A$1:$B$500,2,FALSE))," ",VLOOKUP(A1,SHEET2!$A$1:$b$500,2,FALSE))

this set to have 500 indg

"Ian" wrote:

Hi I am very much an amateur with excel I only really use it to build product
sheets and recipe templates. I am basically trying to do this and I can't
seem to figure it out ( I don't speak excel too well)....

I have a recipe card on sheet 2
In cell A10 I would type in an ingredient "flour"
and what I would like it to do is put my cost per/g in cell g10 which is
linked to my master price list
here is the catch I have over 300+ recipes and I don't want to manually link
all of my G cells with the master price list
I would like to copy and paste my recipes into my current template and link
corresponding words with the appropriate cell on a different sheet. Is this
possible?

Arrays/vlookup???
 

" wrote:

OK you want to type in the indgredent in one cell and then the
cost pe grams will auto popluate in another cell.
OK on sheet two column A list you indg. in column list the cost per gram.

ok so in sheet
lets say col a is the ingredent and colm b is the grams.
in column b type
=IF(ISNA(VLOOKUP(a1,SHEET2!$A$1:$B$500,2,FALSE))," ",VLOOKUP(A1,SHEET2!$A$1:$b$500,2,FALSE))

this set to have 500 indg

"Ian" wrote:

Hi I am very much an amateur with excel I only really use it to build product
sheets and recipe templates. I am basically trying to do this and I can't
seem to figure it out ( I don't speak excel too well)....

I have a recipe card on sheet 2
In cell A10 I would type in an ingredient "flour"
and what I would like it to do is put my cost per/g in cell g10 which is
linked to my master price list
here is the catch I have over 300+ recipes and I don't want to manually link
all of my G cells with the master price list
I would like to copy and paste my recipes into my current template and link
corresponding words with the appropriate cell on a different sheet. Is this
possible?


OK this is still a bit confusing let me show you what i was trying to do which didn't work this is an example....
this look up was in my recipe card temple on sheet 2

=LOOKUP(A10{"Almond","Bananas","Bananas2","Butter" ,"Chocdark","Cream","Currants","Flour",€�Honey"," Cinamon","Bpowder","Milk","Pecan","Strawberry","Su gar","Vanilla","Walnut","Yeast"},{Sheet1!H11,Sheet 1!H3,Sheet1!H5,Sheet1!H28,Sheet1!H31,Sheet1!H13,Sh eet1!H6,Sheet1!H30,Sheet1!H14,Sheet1!H15,Sheet1!H1 6,Sheet1!H17,Sheet1!H18,Sheet1!H19,Sheet1!H21,Shee t1!H20,Sheet1!H29,Sheet1!H7,Sheet1!H22})

I was using my recipe template(sheet 2) on a different sheet then my master
price list(sheet 1) so i wanted the cells in G 1-50*(sheet 2) on my recipe
card to be linked to the price on the master price list I was also hoping to
be able to just type in an ingredient on my recipe card template in cell A
1-50*(sheet 2) and it would automatically link my G cells(sheet 2).
Thanks ian

Arrays/vlookup???
 

" wrote:

OK you want to type in the indgredent in one cell and then the
cost pe grams will auto popluate in another cell.
OK on sheet two column A list you indg. in column list the cost per gram.

ok so in sheet
lets say col a is the ingredent and colm b is the grams.
in column b type
=IF(ISNA(VLOOKUP(a1,SHEET2!$A$1:$B$500,2,FALSE))," ",VLOOKUP(A1,SHEET2!$A$1:$b$500,2,FALSE))

this set to have 500 indg

"Ian" wrote:

Hi I am very much an amateur with excel I only really use it to build product
sheets and recipe templates. I am basically trying to do this and I can't
seem to figure it out ( I don't speak excel too well)....

I have a recipe card on sheet 2
In cell A10 I would type in an ingredient "flour"
and what I would like it to do is put my cost per/g in cell g10 which is
linked to my master price list
here is the catch I have over 300+ recipes and I don't want to manually link
all of my G cells with the master price list
I would like to copy and paste my recipes into my current template and link
corresponding words with the appropriate cell on a different sheet. Is this
possible?


this is an example of the look up i used: its on my recipe card template on sheet 2 or 3 or what ever recipe car it corresponds to. what I was trying to accomplish was whenever i typed in and ingredient into cell A 1-50*(recipe card template) it would put the cost per gram in cell G1-50* (recipe card template)
but it needs to gather that info from cell H 1-1000* on sheet 1(master price
list)

=LOOKUP(A10{"Almond","Bananas","Bananas2","Butter" ,"Chocdark","Cream","Currants","Flour",€�Honey"," Cinamon","Bpowder","Milk","Pecan","Strawberry","Su gar","Vanilla","Walnut","Yeast"},{Sheet1!H11,Sheet 1!H3,Sheet1!H5,Sheet1!H28,Sheet1!H31,Sheet1!H13,Sh eet1!H6,Sheet1!H30,Sheet1!H14,Sheet1!H15,Sheet1!H1 6,Sheet1!H17,Sheet1!H18,Sheet1!H19,Sheet1!H21,Shee t1!H20,Sheet1!H29,Sheet1!H7,Sheet1!H22})

If I use this

=IF(ISNA(VLOOKUP(a1,SHEET2!$A$1:$B$500,2,FALSE))," ",VLOOKUP(A1,SHEET2!$A$1:$b$500,2,FALSE))

and change my master price list cell order from H (price per g)
to B (price per gram) how would i get that info to correlate to a diff sheet??
thanks Ian

Arrays/vlookup???
 

that is one very long formula. it tought to understand
if you like ill take a look at it


"Ian" wrote:



" wrote:

OK you want to type in the indgredent in one cell and then the
cost pe grams will auto popluate in another cell.
OK on sheet two column A list you indg. in column list the cost per gram.

ok so in sheet
lets say col a is the ingredent and colm b is the grams.
in column b type
=IF(ISNA(VLOOKUP(a1,SHEET2!$A$1:$B$500,2,FALSE))," ",VLOOKUP(A1,SHEET2!$A$1:$b$500,2,FALSE))

this set to have 500 indg

"Ian" wrote:

Hi I am very much an amateur with excel I only really use it to build product
sheets and recipe templates. I am basically trying to do this and I can't
seem to figure it out ( I don't speak excel too well)....

I have a recipe card on sheet 2
In cell A10 I would type in an ingredient "flour"
and what I would like it to do is put my cost per/g in cell g10 which is
linked to my master price list
here is the catch I have over 300+ recipes and I don't want to manually link
all of my G cells with the master price list
I would like to copy and paste my recipes into my current template and link
corresponding words with the appropriate cell on a different sheet. Is this
possible?


OK this is still a bit confusing let me show you what i was trying to do which didn't work this is an example....
this look up was in my recipe card temple on sheet 2

=LOOKUP(A10{"Almond","Bananas","Bananas2","Butter" ,"Chocdark","Cream","Currants","Flour",€�Honey"," Cinamon","Bpowder","Milk","Pecan","Strawberry","Su gar","Vanilla","Walnut","Yeast"},{Sheet1!H11,Sheet 1!H3,Sheet1!H5,Sheet1!H28,Sheet1!H31,Sheet1!H13,Sh eet1!H6,Sheet1!H30,Sheet1!H14,Sheet1!H15,Sheet1!H1 6,Sheet1!H17,Sheet1!H18,Sheet1!H19,Sheet1!H21,Shee t1!H20,Sheet1!H29,Sheet1!H7,Sheet1!H22})

I was using my recipe template(sheet 2) on a different sheet then my master
price list(sheet 1) so i wanted the cells in G 1-50*(sheet 2) on my recipe
card to be linked to the price on the master price list I was also hoping to
be able to just type in an ingredient on my recipe card template in cell A
1-50*(sheet 2) and it would automatically link my G cells(sheet 2).
Thanks ian

Arrays/vlookup???
 

Hi
just wanted to thank you for all the help the formula worked perfectly!!
Cheers,
Ian