software

I am trying to create a spread sheet that in one column material is either
entered or picked from a drop down list and depending on what is selected or
entered, the cost that corresponds to that particular material is displayed

Try this:

Beginning in Cell A1 on another sheet, I'll assume Sheet2, create a 2-col
list.
First column: PartNum
Second Column: Cost

Then, on sheet1....
A1: (some part number)
B1: =VLOOKUP(A1,Sheet2!A1:B100,2,0)

That formula will try to find the part number in Cell A1 in the first column
of the list on Sheet2. If it finds a match, it will return the corresponding
cost value.

Note: If you don't want errors to display for partnumbers that are not in
the list, use this version:
B1: =IF(ISERROR(VLOOKUP(A1,Sheet2!A1:B100,2,0)),quot;No
Matchquot;,VLOOKUP(A1,Sheet2!A1:B100,2,0))

Of course, adjust range references to suit your situation.

Does that help?

***********
Regards,
Ronquot;mn_taterquot; wrote:

gt; I am trying to create a spread sheet that in one column material is either
gt; entered or picked from a drop down list and depending on what is selected or
gt; entered, the cost that corresponds to that particular material is displayed


Thank you - That definalty helps me out!!
quot;Ron Coderrequot; wrote:

gt; Try this:
gt;
gt; Beginning in Cell A1 on another sheet, I'll assume Sheet2, create a 2-col
gt; list.
gt; First column: PartNum
gt; Second Column: Cost
gt;
gt; Then, on sheet1....
gt; A1: (some part number)
gt; B1: =VLOOKUP(A1,Sheet2!A1:B100,2,0)
gt;
gt; That formula will try to find the part number in Cell A1 in the first column
gt; of the list on Sheet2. If it finds a match, it will return the corresponding
gt; cost value.
gt;
gt; Note: If you don't want errors to display for partnumbers that are not in
gt; the list, use this version:
gt; B1: =IF(ISERROR(VLOOKUP(A1,Sheet2!A1:B100,2,0)),quot;No
gt; Matchquot;,VLOOKUP(A1,Sheet2!A1:B100,2,0))
gt;
gt; Of course, adjust range references to suit your situation.
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt;
gt; quot;mn_taterquot; wrote:
gt;
gt; gt; I am trying to create a spread sheet that in one column material is either
gt; gt; entered or picked from a drop down list and depending on what is selected or
gt; gt; entered, the cost that corresponds to that particular material is displayed

Ron

I just have to leap in here, albeit with some trepidation since your advice is
spot on in most cases.

The ISERROR function masks all errors.

The ISNA function might be a better function in a VLOOKUP formula.

I can't see how any other error except #N/A would arise in this particular
case but OP should not get in the habit of using the ISERROR for all formulas.

If OP had a formula like

=IF(ISERROR(VLOOKUP(D1,A1:B8,2,FALSE)/VLOOKUP(D2,A1:B8,2,FALSE)),quot;no
matchquot;,VLOOKUP(D1,A1:B8,2,FALSE)/VLOOKUP(D2,A1:B8,2,FALSE))

A value could be found and returned for each VLOOKUP statement but if value
returned for VLOOKUP(D2,A1:B8,2,FALSE) was zero or blank he could get a
#DIV/0! error which would be masked by the ISERROR and get quot;no matchquot; even
though #N/A was not the error.Gord Dibben Excel MVPOn Thu, 15 Dec 2005 09:51:03 -0800, quot;Ron Coderrequot;
gt; wrote:

gt;Try this:
gt;
gt;Beginning in Cell A1 on another sheet, I'll assume Sheet2, create a 2-col
gt;list.
gt;First column: PartNum
gt;Second Column: Cost
gt;
gt;Then, on sheet1....
gt;A1: (some part number)
gt;B1: =VLOOKUP(A1,Sheet2!A1:B100,2,0)
gt;
gt;That formula will try to find the part number in Cell A1 in the first column
gt;of the list on Sheet2. If it finds a match, it will return the corresponding
gt;cost value.
gt;
gt;Note: If you don't want errors to display for partnumbers that are not in
gt;the list, use this version:
gt;B1: =IF(ISERROR(VLOOKUP(A1,Sheet2!A1:B100,2,0)),quot;No
gt;Matchquot;,VLOOKUP(A1,Sheet2!A1:B100,2,0))
gt;
gt;Of course, adjust range references to suit your situation.
gt;
gt;Does that help?
gt;
gt;***********
gt;Regards,
gt;Ron
gt;
gt;
gt;quot;mn_taterquot; wrote:
gt;
gt;gt; I am trying to create a spread sheet that in one column material is either
gt;gt; entered or picked from a drop down list and depending on what is selected or
gt;gt; entered, the cost that corresponds to that particular material is displayed

You know what I like about this forum?

I can never get away with being lazy in my advice.
Per usual, Gord, your comment is spot on. Thanks for giving me a newspaper
over the snout....I deserved it.

***********
Best Regards,
Ronquot;Gord Dibbenquot; wrote:

gt; Ron
gt;
gt; I just have to leap in here, albeit with some trepidation since your advice is
gt; spot on in most cases.
gt;
gt; The ISERROR function masks all errors.
gt;
gt; The ISNA function might be a better function in a VLOOKUP formula.
gt;
gt; I can't see how any other error except #N/A would arise in this particular
gt; case but OP should not get in the habit of using the ISERROR for all formulas.
gt;
gt; If OP had a formula like
gt;
gt; =IF(ISERROR(VLOOKUP(D1,A1:B8,2,FALSE)/VLOOKUP(D2,A1:B8,2,FALSE)),quot;no
gt; matchquot;,VLOOKUP(D1,A1:B8,2,FALSE)/VLOOKUP(D2,A1:B8,2,FALSE))
gt;
gt; A value could be found and returned for each VLOOKUP statement but if value
gt; returned for VLOOKUP(D2,A1:B8,2,FALSE) was zero or blank he could get a
gt; #DIV/0! error which would be masked by the ISERROR and get quot;no matchquot; even
gt; though #N/A was not the error.
gt;
gt;
gt; Gord Dibben Excel MVP
gt;
gt;
gt; On Thu, 15 Dec 2005 09:51:03 -0800, quot;Ron Coderrequot;
gt; gt; wrote:
gt;
gt; gt;Try this:
gt; gt;
gt; gt;Beginning in Cell A1 on another sheet, I'll assume Sheet2, create a 2-col
gt; gt;list.
gt; gt;First column: PartNum
gt; gt;Second Column: Cost
gt; gt;
gt; gt;Then, on sheet1....
gt; gt;A1: (some part number)
gt; gt;B1: =VLOOKUP(A1,Sheet2!A1:B100,2,0)
gt; gt;
gt; gt;That formula will try to find the part number in Cell A1 in the first column
gt; gt;of the list on Sheet2. If it finds a match, it will return the corresponding
gt; gt;cost value.
gt; gt;
gt; gt;Note: If you don't want errors to display for partnumbers that are not in
gt; gt;the list, use this version:
gt; gt;B1: =IF(ISERROR(VLOOKUP(A1,Sheet2!A1:B100,2,0)),quot;No
gt; gt;Matchquot;,VLOOKUP(A1,Sheet2!A1:B100,2,0))
gt; gt;
gt; gt;Of course, adjust range references to suit your situation.
gt; gt;
gt; gt;Does that help?
gt; gt;
gt; gt;***********
gt; gt;Regards,
gt; gt;Ron
gt; gt;
gt; gt;
gt; gt;quot;mn_taterquot; wrote:
gt; gt;
gt; gt;gt; I am trying to create a spread sheet that in one column material is either
gt; gt;gt; entered or picked from a drop down list and depending on what is selected or
gt; gt;gt; entered, the cost that corresponds to that particular material is displayed
gt;

You know what I like about all the Excel groups.

Pretty much Everything!Gord

On Thu, 15 Dec 2005 12:52:02 -0800, quot;Ron Coderrequot;
gt; wrote:

gt;You know what I like about this forum?
gt;
gt;I can never get away with being lazy in my advice.
gt;Per usual, Gord, your comment is spot on. Thanks for giving me a newspaper
gt;over the snout....I deserved it.
gt;
gt;***********
gt;Best Regards,
gt;Ron
gt;
gt;
gt;quot;Gord Dibbenquot; wrote:
gt;
gt;gt; Ron
gt;gt;
gt;gt; I just have to leap in here, albeit with some trepidation since your advice is
gt;gt; spot on in most cases.
gt;gt;
gt;gt; The ISERROR function masks all errors.
gt;gt;
gt;gt; The ISNA function might be a better function in a VLOOKUP formula.
gt;gt;
gt;gt; I can't see how any other error except #N/A would arise in this particular
gt;gt; case but OP should not get in the habit of using the ISERROR for all formulas.
gt;gt;
gt;gt; If OP had a formula like
gt;gt;
gt;gt; =IF(ISERROR(VLOOKUP(D1,A1:B8,2,FALSE)/VLOOKUP(D2,A1:B8,2,FALSE)),quot;no
gt;gt; matchquot;,VLOOKUP(D1,A1:B8,2,FALSE)/VLOOKUP(D2,A1:B8,2,FALSE))
gt;gt;
gt;gt; A value could be found and returned for each VLOOKUP statement but if value
gt;gt; returned for VLOOKUP(D2,A1:B8,2,FALSE) was zero or blank he could get a
gt;gt; #DIV/0! error which would be masked by the ISERROR and get quot;no matchquot; even
gt;gt; though #N/A was not the error.
gt;gt;
gt;gt;
gt;gt; Gord Dibben Excel MVP
gt;gt;
gt;gt;
gt;gt; On Thu, 15 Dec 2005 09:51:03 -0800, quot;Ron Coderrequot;
gt;gt; gt; wrote:
gt;gt;
gt;gt; gt;Try this:
gt;gt; gt;
gt;gt; gt;Beginning in Cell A1 on another sheet, I'll assume Sheet2, create a 2-col
gt;gt; gt;list.
gt;gt; gt;First column: PartNum
gt;gt; gt;Second Column: Cost
gt;gt; gt;
gt;gt; gt;Then, on sheet1....
gt;gt; gt;A1: (some part number)
gt;gt; gt;B1: =VLOOKUP(A1,Sheet2!A1:B100,2,0)
gt;gt; gt;
gt;gt; gt;That formula will try to find the part number in Cell A1 in the first column
gt;gt; gt;of the list on Sheet2. If it finds a match, it will return the corresponding
gt;gt; gt;cost value.
gt;gt; gt;
gt;gt; gt;Note: If you don't want errors to display for partnumbers that are not in
gt;gt; gt;the list, use this version:
gt;gt; gt;B1: =IF(ISERROR(VLOOKUP(A1,Sheet2!A1:B100,2,0)),quot;No
gt;gt; gt;Matchquot;,VLOOKUP(A1,Sheet2!A1:B100,2,0))
gt;gt; gt;
gt;gt; gt;Of course, adjust range references to suit your situation.
gt;gt; gt;
gt;gt; gt;Does that help?
gt;gt; gt;
gt;gt; gt;***********
gt;gt; gt;Regards,
gt;gt; gt;Ron
gt;gt; gt;
gt;gt; gt;
gt;gt; gt;quot;mn_taterquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; I am trying to create a spread sheet that in one column material is either
gt;gt; gt;gt; entered or picked from a drop down list and depending on what is selected or
gt;gt; gt;gt; entered, the cost that corresponds to that particular material is displayed
gt;gt;