Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
mn_tater
 
Posts: n/a
Default Creating lookup formulas for material cost spreadsheet

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default Creating lookup formulas for material cost spreadsheet

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)),"No
Match",VLOOKUP(A1,Sheet2!A1:B100,2,0))

Of course, adjust range references to suit your situation.

Does that help?

***********
Regards,
Ron


"mn_tater" wrote:

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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
mn_tater
 
Posts: n/a
Default Creating lookup formulas for material cost spreadsheet


Thank you - That definalty helps me out!!
"Ron Coderre" wrote:

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)),"No
Match",VLOOKUP(A1,Sheet2!A1:B100,2,0))

Of course, adjust range references to suit your situation.

Does that help?

***********
Regards,
Ron


"mn_tater" wrote:

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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gord Dibben
 
Posts: n/a
Default Creating lookup formulas for material cost spreadsheet

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)),"no
match",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 "no match" even
though #N/A was not the error.


Gord Dibben Excel MVP


On Thu, 15 Dec 2005 09:51:03 -0800, "Ron Coderre"
wrote:

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)),"No
Match",VLOOKUP(A1,Sheet2!A1:B100,2,0))

Of course, adjust range references to suit your situation.

Does that help?

***********
Regards,
Ron


"mn_tater" wrote:

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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default Creating lookup formulas for material cost spreadsheet

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,
Ron


"Gord Dibben" wrote:

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)),"no
match",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 "no match" even
though #N/A was not the error.


Gord Dibben Excel MVP


On Thu, 15 Dec 2005 09:51:03 -0800, "Ron Coderre"
wrote:

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)),"No
Match",VLOOKUP(A1,Sheet2!A1:B100,2,0))

Of course, adjust range references to suit your situation.

Does that help?

***********
Regards,
Ron


"mn_tater" wrote:

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




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gord Dibben
 
Posts: n/a
Default Creating lookup formulas for material cost spreadsheet

You know what I like about all the Excel groups.

Pretty much Everything!


Gord

On Thu, 15 Dec 2005 12:52:02 -0800, "Ron Coderre"
wrote:

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,
Ron


"Gord Dibben" wrote:

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)),"no
match",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 "no match" even
though #N/A was not the error.


Gord Dibben Excel MVP


On Thu, 15 Dec 2005 09:51:03 -0800, "Ron Coderre"
wrote:

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)),"No
Match",VLOOKUP(A1,Sheet2!A1:B100,2,0))

Of course, adjust range references to suit your situation.

Does that help?

***********
Regards,
Ron


"mn_tater" wrote:

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating Formulas for overtime SumrGrl318 Excel Worksheet Functions 1 March 18th 05 09:19 PM
creating multiple files from a spreadsheet Carol Summa Excel Discussion (Misc queries) 1 March 17th 05 01:07 PM
creating spreadsheet with colunms for names adresses & zipcodes neilabu Excel Discussion (Misc queries) 4 March 4th 05 01:53 AM
Creating Spreadsheet Calculations Glenn Excel Discussion (Misc queries) 2 December 17th 04 05:01 PM
Creating a summary from existing spreadsheet data ... NP Excel Worksheet Functions 8 October 29th 04 02:39 PM


All times are GMT +1. The time now is 06:12 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"