Arrays/vlookup???

  • Thread starter Thread starter Ian
  • Start date Start date
I

Ian

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 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 typ
=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
 
[email protected] said:
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


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....
Click to expand...
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","Sugar","Vanilla","Walnut","Yeast"},{Sheet1!H11,Sheet1!H3,Sheet1!H5,Sheet1!H28,Sheet1!H31,Sheet1!H13,Sheet1!H6,Sheet1!H30,Sheet1!H14,Sheet1!H15,Sheet1!H16,Sheet1!H17,Sheet1!H18,Sheet1!H19,Sheet1!H21,Sheet1!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
 
[email protected] said:
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
Click to expand...
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","Sugar","Vanilla","Walnut","Yeast"},{Sheet1!H11,Sheet1!H3,Sheet1!H5,Sheet1!H28,Sheet1!H31,Sheet1!H13,Sheet1!H6,Sheet1!H30,Sheet1!H14,Sheet1!H15,Sheet1!H16,Sheet1!H17,Sheet1!H18,Sheet1!H19,Sheet1!H21,Sheet1!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
 
that is one very long formula. it tought to understand
if you like ill take a look at it
(e-mail address removed)
 
Back
Top