| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using VLOOKUP to find product from 3 worksheets to put on one shee
I use excel to do quotes for customers (i own a retail bathroom renovation
company).I have my product set up one one sheet called "Shower" the next sheet called "Bathtub" and the other sheet called "Bathroom". I need to find the product I enter on those sheets and put them on my quote on a seperate sheet. Currently I am only using one sheet called "Build a quote" with all the product on it, but I have way to big of a product list now and recently seperated it into the 3 sheets, my code currently looks like this =VLOOKUP(A1,BUILDAQUOTE!$B$2:$J$260,4,FALSE) now I need to make the code so that it will look up from the 3 pages, not just one. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using VLOOKUP to find product from 3 worksheets to put on one shee
try:
=IF(ISERROR(MATCH(A1,Shower!B2:B260,0)),"",VLOOKUP (A1,Shower!B2:J260,4,FALSE))&IF(ISERROR(MATCH(A1,B athtub!B2:B260,0)),"",VLOOKUP(A1,Bathtub!B2:J260,4 ,FALSE))&IF(ISERROR(MATCH(A1,Bathroom!B2:B260,0)), "",VLOOKUP(A1,Bathroom!B2:J260,4,FALSE)) "Bathroom Reno Chick" skrev: I use excel to do quotes for customers (i own a retail bathroom renovation company).I have my product set up one one sheet called "Shower" the next sheet called "Bathtub" and the other sheet called "Bathroom". I need to find the product I enter on those sheets and put them on my quote on a seperate sheet. Currently I am only using one sheet called "Build a quote" with all the product on it, but I have way to big of a product list now and recently seperated it into the 3 sheets, my code currently looks like this =VLOOKUP(A1,BUILDAQUOTE!$B$2:$J$260,4,FALSE) now I need to make the code so that it will look up from the 3 pages, not just one. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using VLOOKUP to find product from 3 worksheets to put on one
Another way
=SUMPRODUCT((Shower!B2:B260=A1)*(Shower!E2:E260)+( Bathtub!B2:B260=A1)*(Bathtub!E2:E260)+(Bathroom!B2 :B260=A1)*(Bathroom!E2:E260)) "excelent" skrev: try: =IF(ISERROR(MATCH(A1,Shower!B2:B260,0)),"",VLOOKUP (A1,Shower!B2:J260,4,FALSE))&IF(ISERROR(MATCH(A1,B athtub!B2:B260,0)),"",VLOOKUP(A1,Bathtub!B2:J260,4 ,FALSE))&IF(ISERROR(MATCH(A1,Bathroom!B2:B260,0)), "",VLOOKUP(A1,Bathroom!B2:J260,4,FALSE)) "Bathroom Reno Chick" skrev: I use excel to do quotes for customers (i own a retail bathroom renovation company).I have my product set up one one sheet called "Shower" the next sheet called "Bathtub" and the other sheet called "Bathroom". I need to find the product I enter on those sheets and put them on my quote on a seperate sheet. Currently I am only using one sheet called "Build a quote" with all the product on it, but I have way to big of a product list now and recently seperated it into the 3 sheets, my code currently looks like this =VLOOKUP(A1,BUILDAQUOTE!$B$2:$J$260,4,FALSE) now I need to make the code so that it will look up from the 3 pages, not just one. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using VLOOKUP to find product from 3 worksheets to put on one shee
MySheets is a define name
=SUMPRODUCT(SUMIF(INDIRECT("'"&MySheets&"'!B2:B260 "),C1,INDIRECT("'"&MySheets&"'!E2:E260"))) "Bathroom Reno Chick" wrote: I use excel to do quotes for customers (i own a retail bathroom renovation company).I have my product set up one one sheet called "Shower" the next sheet called "Bathtub" and the other sheet called "Bathroom". I need to find the product I enter on those sheets and put them on my quote on a seperate sheet. Currently I am only using one sheet called "Build a quote" with all the product on it, but I have way to big of a product list now and recently seperated it into the 3 sheets, my code currently looks like this =VLOOKUP(A1,BUILDAQUOTE!$B$2:$J$260,4,FALSE) now I need to make the code so that it will look up from the 3 pages, not just one. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| a map to show others where to find product on shelves | Excel Discussion (Misc queries) | |||
| Totalling Values From The Same Cell In 12 Worksheets Onto New Shee | Excel Worksheet Functions | |||
| Totalling Values From The Same Cell In 12 Worksheets Onto New Shee | Setting up and Configuration of Excel | |||
| VLOOKUP with PRODUCT functionality? | New Users to Excel | |||
| How do I group worksheets (Lotus 123 function is "Sheet>Group Shee | Excel Worksheet Functions | |||
Linear Mode
