software

I am building a price sheet that I need to come up with a formula that can
figure a material coat if the part is one of 13 different types of material.
I want to be able to update my material pricing on sheet 2 andd have the
sheet recalculate it automaticly for each part.
I have quot;Aquot; column as Material type(abreviation like SS for stainless steel),
quot;Bquot; column as weight of the part, and Sheet 2 column quot;Aquot; as the price for the
material. My formula (column Cquot;)looks like : =IF(A1=quot;SSquot;,B1*sheet2!A1,.....)
and so forth for each of the 13 types of material. I can get it to work for
the first eight types of material but anything past that it gives me an
error. I also would like to display quot;errorquot; in the cell if someone does not
enter the material abbreviation correctly .
Any help would be greatly appreciated. Thanks.

Here is what I would suggest:

On Sheet 2, list all of your materials in Column A and corresponding prices
in column B.

Now, for your formula in Column C of Sheet 1:

=IF(ISERROR(VLOOKUP(A1,Sheet2!$A$1:$B$100,2,FALSE) *B1),quot;Errorquot;,VLOOKUP(A1,Sheet2!$A$1:$B$100,2,FALSE )*B1)

I just assumed 100 rows in this example, you may have more or less. Adjust
accordingly.

HTH,
Elkar

quot;N Dependablequot; wrote:

gt; I am building a price sheet that I need to come up with a formula that can
gt; figure a material coat if the part is one of 13 different types of material.
gt; I want to be able to update my material pricing on sheet 2 andd have the
gt; sheet recalculate it automaticly for each part.
gt; I have quot;Aquot; column as Material type(abreviation like SS for stainless steel),
gt; quot;Bquot; column as weight of the part, and Sheet 2 column quot;Aquot; as the price for the
gt; material. My formula (column Cquot;)looks like : =IF(A1=quot;SSquot;,B1*sheet2!A1,.....)
gt; and so forth for each of the 13 types of material. I can get it to work for
gt; the first eight types of material but anything past that it gives me an
gt; error. I also would like to display quot;errorquot; in the cell if someone does not
gt; enter the material abbreviation correctly .
gt; Any help would be greatly appreciated. Thanks.

Hi!

Here's another way to write that formula:

=IF(COUNTIF(Sheet2!A$1:A$100,A1),VLOOKUP(A1,Sheet2 !A$1:B$100,2,0)*B1),quot;Errorquot;)

Biff

quot;Elkarquot; gt; wrote in message
...
gt; Here is what I would suggest:
gt;
gt; On Sheet 2, list all of your materials in Column A and corresponding
gt; prices
gt; in column B.
gt;
gt; Now, for your formula in Column C of Sheet 1:
gt;
gt; =IF(ISERROR(VLOOKUP(A1,Sheet2!$A$1:$B$100,2,FALSE) *B1),quot;Errorquot;,VLOOKUP(A1,Sheet2!$A$1:$B$100,2,FALSE )*B1)
gt;
gt; I just assumed 100 rows in this example, you may have more or less.
gt; Adjust
gt; accordingly.
gt;
gt; HTH,
gt; Elkar
gt;
gt; quot;N Dependablequot; wrote:
gt;
gt;gt; I am building a price sheet that I need to come up with a formula that
gt;gt; can
gt;gt; figure a material coat if the part is one of 13 different types of
gt;gt; material.
gt;gt; I want to be able to update my material pricing on sheet 2 andd have the
gt;gt; sheet recalculate it automaticly for each part.
gt;gt; I have quot;Aquot; column as Material type(abreviation like SS for stainless
gt;gt; steel),
gt;gt; quot;Bquot; column as weight of the part, and Sheet 2 column quot;Aquot; as the price for
gt;gt; the
gt;gt; material. My formula (column Cquot;)looks like :
gt;gt; =IF(A1=quot;SSquot;,B1*sheet2!A1,.....)
gt;gt; and so forth for each of the 13 types of material. I can get it to work
gt;gt; for
gt;gt; the first eight types of material but anything past that it gives me an
gt;gt; error. I also would like to display quot;errorquot; in the cell if someone does
gt;gt; not
gt;gt; enter the material abbreviation correctly .
gt;gt; Any help would be greatly appreciated. Thanks.
Thank you Thank you Thank you!!!!!!!!
--
Nathan Hoviousquot;Elkarquot; wrote:

gt; Here is what I would suggest:
gt;
gt; On Sheet 2, list all of your materials in Column A and corresponding prices
gt; in column B.
gt;
gt; Now, for your formula in Column C of Sheet 1:
gt;
gt; =IF(ISERROR(VLOOKUP(A1,Sheet2!$A$1:$B$100,2,FALSE) *B1),quot;Errorquot;,VLOOKUP(A1,Sheet2!$A$1:$B$100,2,FALSE )*B1)
gt;
gt; I just assumed 100 rows in this example, you may have more or less. Adjust
gt; accordingly.
gt;
gt; HTH,
gt; Elkar
gt;
gt; quot;N Dependablequot; wrote:
gt;
gt; gt; I am building a price sheet that I need to come up with a formula that can
gt; gt; figure a material coat if the part is one of 13 different types of material.
gt; gt; I want to be able to update my material pricing on sheet 2 andd have the
gt; gt; sheet recalculate it automaticly for each part.
gt; gt; I have quot;Aquot; column as Material type(abreviation like SS for stainless steel),
gt; gt; quot;Bquot; column as weight of the part, and Sheet 2 column quot;Aquot; as the price for the
gt; gt; material. My formula (column Cquot;)looks like : =IF(A1=quot;SSquot;,B1*sheet2!A1,.....)
gt; gt; and so forth for each of the 13 types of material. I can get it to work for
gt; gt; the first eight types of material but anything past that it gives me an
gt; gt; error. I also would like to display quot;errorquot; in the cell if someone does not
gt; gt; enter the material abbreviation correctly .
gt; gt; Any help would be greatly appreciated. Thanks.