Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2 Workbooks and 6 Worksheets - Need to Populate 1 Workbook
I have two EXCEL 2000 workbooks( Estimates.xls and Prices.xls).
Workbook 'Estimates.xls' has 1 worksheet with empty cells (C&D) that need populating if columns A&B are filled in, see below. (snapshot of ESTIMATES.xls) A B C D 1 Product Batch Description Est Price 2 400 01 ____ $____ 3 400 03 ____ $____ 4 600 01 ____ $____ 5 200 02 ____ $____ 6 900 01 ____ $____ 7 900 02 ____ $____ Workbook 'Prices.xls' has 6 worksheets that are lists of different brands with prices used for referencing the 'Estimate.xls' workbook. Its sheets are titled 'Brand 1, Brand 2 thru Brand 6', see below. (snapshot of PRICES.xls, specifically Brand 1 worksheet) A B C D 1 Product Batch Description Est Price 2 200 01 Red $45 3 300 03 Blue Small $75 4 600 01 Orange Med $47 5 900 02 Large $87 6 400 01 Small $96 7 900 01 Medium $15 (PRICES.xls, Brand 2 worksheet) A B C D 1 Product Batch Description Est Price 2 100 01 Green $22 3 800 03 Brown $14 4 400 01 Black $95 5 200 02 XL $67 6 900 03 XXL $98 7 700 02 XXXL $10 (this is a snapshot of PRICES.xls, Brand 3 worksheet) A B C D 1 Product Batch Description Price 2 500 01 Cyan $44 3 400 03 Magenta $29 Worksheets 4 thru 6 are very similar. Any help with this is appreciated. (ps: I'm a beginner so please use details in response) Michelle |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2 Workbooks and 6 Worksheets - Need to Populate 1 Workbook
You would need to use the VLOOKUP function to do what you're trying to do.
However, one suggestion would be to have one workbook rather than two for this task. The reason for this is that the syntax for VLOOKUP formulas is rather complex; it is made more so by having a link to an external workbook. Since you say you're a beginner you may wish to save yourself the headache of auditing VLOOKUPS which reference external workbooks. In any event, this site explains how VLOOKUP works: http://www.techonthenet.com/excel/formulas/vlookup.php Best thing for you to do would go to the site, try the VLOOKUP, and, if you run into problems, reply to this post with the VLOOKUP syntax you are using and someone can help you. Dave -- Brevity is the soul of wit. "MichelleS" wrote: I have two EXCEL 2000 workbooks( Estimates.xls and Prices.xls). Workbook 'Estimates.xls' has 1 worksheet with empty cells (C&D) that need populating if columns A&B are filled in, see below. (snapshot of ESTIMATES.xls) A B C D 1 Product Batch Description Est Price 2 400 01 ____ $____ 3 400 03 ____ $____ 4 600 01 ____ $____ 5 200 02 ____ $____ 6 900 01 ____ $____ 7 900 02 ____ $____ Workbook 'Prices.xls' has 6 worksheets that are lists of different brands with prices used for referencing the 'Estimate.xls' workbook. Its sheets are titled 'Brand 1, Brand 2 thru Brand 6', see below. (snapshot of PRICES.xls, specifically Brand 1 worksheet) A B C D 1 Product Batch Description Est Price 2 200 01 Red $45 3 300 03 Blue Small $75 4 600 01 Orange Med $47 5 900 02 Large $87 6 400 01 Small $96 7 900 01 Medium $15 (PRICES.xls, Brand 2 worksheet) A B C D 1 Product Batch Description Est Price 2 100 01 Green $22 3 800 03 Brown $14 4 400 01 Black $95 5 200 02 XL $67 6 900 03 XXL $98 7 700 02 XXXL $10 (this is a snapshot of PRICES.xls, Brand 3 worksheet) A B C D 1 Product Batch Description Price 2 500 01 Cyan $44 3 400 03 Magenta $29 Worksheets 4 thru 6 are very similar. Any help with this is appreciated. (ps: I'm a beginner so please use details in response) Michelle |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2 Workbooks and 6 Worksheets - Need to Populate 1 Workbook
Thank you Dave, but I tried the VLOOKUP and it does not work for all of my 6
sheets nor for the 2 criteria search (Product and Batch Code). Also, for accounting purposes I cannot move any data, because other departments reference that same Price.xls workbook. Michelle "Dave F" wrote: You would need to use the VLOOKUP function to do what you're trying to do. However, one suggestion would be to have one workbook rather than two for this task. The reason for this is that the syntax for VLOOKUP formulas is rather complex; it is made more so by having a link to an external workbook. Since you say you're a beginner you may wish to save yourself the headache of auditing VLOOKUPS which reference external workbooks. In any event, this site explains how VLOOKUP works: http://www.techonthenet.com/excel/formulas/vlookup.php Best thing for you to do would go to the site, try the VLOOKUP, and, if you run into problems, reply to this post with the VLOOKUP syntax you are using and someone can help you. Dave -- Brevity is the soul of wit. "MichelleS" wrote: I have two EXCEL 2000 workbooks( Estimates.xls and Prices.xls). Workbook 'Estimates.xls' has 1 worksheet with empty cells (C&D) that need populating if columns A&B are filled in, see below. (snapshot of ESTIMATES.xls) A B C D 1 Product Batch Description Est Price 2 400 01 ____ $____ 3 400 03 ____ $____ 4 600 01 ____ $____ 5 200 02 ____ $____ 6 900 01 ____ $____ 7 900 02 ____ $____ Workbook 'Prices.xls' has 6 worksheets that are lists of different brands with prices used for referencing the 'Estimate.xls' workbook. Its sheets are titled 'Brand 1, Brand 2 thru Brand 6', see below. (snapshot of PRICES.xls, specifically Brand 1 worksheet) A B C D 1 Product Batch Description Est Price 2 200 01 Red $45 3 300 03 Blue Small $75 4 600 01 Orange Med $47 5 900 02 Large $87 6 400 01 Small $96 7 900 01 Medium $15 (PRICES.xls, Brand 2 worksheet) A B C D 1 Product Batch Description Est Price 2 100 01 Green $22 3 800 03 Brown $14 4 400 01 Black $95 5 200 02 XL $67 6 900 03 XXL $98 7 700 02 XXXL $10 (this is a snapshot of PRICES.xls, Brand 3 worksheet) A B C D 1 Product Batch Description Price 2 500 01 Cyan $44 3 400 03 Magenta $29 Worksheets 4 thru 6 are very similar. Any help with this is appreciated. (ps: I'm a beginner so please use details in response) Michelle |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2 Workbooks and 6 Worksheets - Need to Populate 1 Workbook
excuse me Dave...Michelle,
From your sheet title: Brand 1 : item 6 : Product 400, Batch 01, Description Small, Est.Price $96-- Brand 2 : item 4 : Product 400, Batch 01, Description Black, Est.Price $95-- Your encoding sheet A & B on Estimates.xls may not be enough if you dont add another column LIKE for Brand.... The BRANDS sheet Description is quite strange- some are by sizes, others are by colors....please check first the 6 Brand Sheet Description and Product coding....more power... "Bright minds are blessed to those who share them.."-rsb. "MichelleS" wrote: Thank you Dave, but I tried the VLOOKUP and it does not work for all of my 6 sheets nor for the 2 criteria search (Product and Batch Code). Also, for accounting purposes I cannot move any data, because other departments reference that same Price.xls workbook. Michelle "Dave F" wrote: You would need to use the VLOOKUP function to do what you're trying to do. However, one suggestion would be to have one workbook rather than two for this task. The reason for this is that the syntax for VLOOKUP formulas is rather complex; it is made more so by having a link to an external workbook. Since you say you're a beginner you may wish to save yourself the headache of auditing VLOOKUPS which reference external workbooks. In any event, this site explains how VLOOKUP works: http://www.techonthenet.com/excel/formulas/vlookup.php Best thing for you to do would go to the site, try the VLOOKUP, and, if you run into problems, reply to this post with the VLOOKUP syntax you are using and someone can help you. Dave -- Brevity is the soul of wit. "MichelleS" wrote: I have two EXCEL 2000 workbooks( Estimates.xls and Prices.xls). Workbook 'Estimates.xls' has 1 worksheet with empty cells (C&D) that need populating if columns A&B are filled in, see below. (snapshot of ESTIMATES.xls) A B C D 1 Product Batch Description Est Price 2 400 01 ____ $____ 3 400 03 ____ $____ 4 600 01 ____ $____ 5 200 02 ____ $____ 6 900 01 ____ $____ 7 900 02 ____ $____ Workbook 'Prices.xls' has 6 worksheets that are lists of different brands with prices used for referencing the 'Estimate.xls' workbook. Its sheets are titled 'Brand 1, Brand 2 thru Brand 6', see below. (snapshot of PRICES.xls, specifically Brand 1 worksheet) A B C D 1 Product Batch Description Est Price 2 200 01 Red $45 3 300 03 Blue Small $75 4 600 01 Orange Med $47 5 900 02 Large $87 6 400 01 Small $96 7 900 01 Medium $15 (PRICES.xls, Brand 2 worksheet) A B C D 1 Product Batch Description Est Price 2 100 01 Green $22 3 800 03 Brown $14 4 400 01 Black $95 5 200 02 XL $67 6 900 03 XXL $98 7 700 02 XXXL $10 (this is a snapshot of PRICES.xls, Brand 3 worksheet) A B C D 1 Product Batch Description Price 2 500 01 Cyan $44 3 400 03 Magenta $29 Worksheets 4 thru 6 are very similar. Any help with this is appreciated. (ps: I'm a beginner so please use details in response) Michelle |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2 Workbooks and 6 Worksheets - Need to Populate 1 Workbook
Romelsb,
Thanks for replying but..................... In all honesty, the 'Descriptions', I have provided you are irrelevant, only cell fillers in place of the true info. The sheet names.....Brand 1, Brand 2, etc, etc. are also irrelevant, only sheet name fillers in place of the true info., So do not focus on those....... "My main interest is in finding a way to automatically poplulate cells from one workbook with 6 different sheets to another workbook with 1 sheet." I have tried the VLOOKUP function below with cntrl+shft+enter, it works for only 1 criteria search not both (product# and batch#). Maybe there is another function INDEX MATCH or something that I should be using......... =IF(OR('[Prices.xls]sheet1'!$A$2:$A$3=C2),VLOOKUP(C2,'[Prices.xls]sheet1'!$A$2:$C$3,3,FALSE),IF(OR('[Prices.xls]sheet2'!$A$2:$A$3=C2),VLOOKUP(C2,'[Prices.xls]sheet2'!$A$2:$C$3,3,FALSE),IF(OR('[Prices.xls]sheet3'!$A$2:$A$3=C2),VLOOKUP(C2,'[Prices.xls]sheet3'!$A$2:$C$3,3,FALSE),IF(OR('[Prices.xls]sheet4'!$A$2:$A$3=C2),VLOOKUP(C2,'[Prices.xls]sheet4'!$A$2:$C$3,3,FALSE),IF(OR('[Prices.xls]sheet5'!$A$2:$A$3=C2),VLOOKUP(C2,'[Prices.xls]sheet5'!$A$2:$C$3,3,FALSE),IF(OR('[Prices.xls]sheet6'!$A$2:$A$3=C2),VLOOKUP(C2,'[Prices.xls]sheet6'!$A$2:$C$3,3,FALSE),"")))))) Again Anyone with experience in this area please assist. Thank you much !! "romelsb" wrote: excuse me Dave...Michelle, From your sheet title: Brand 1 : item 6 : Product 400, Batch 01, Description Small, Est.Price $96-- Brand 2 : item 4 : Product 400, Batch 01, Description Black, Est.Price $95-- Your encoding sheet A & B on Estimates.xls may not be enough if you dont add another column LIKE for Brand.... The BRANDS sheet Description is quite strange- some are by sizes, others are by colors....please check first the 6 Brand Sheet Description and Product coding....more power... "Bright minds are blessed to those who share them.."-rsb. "MichelleS" wrote: Thank you Dave, but I tried the VLOOKUP and it does not work for all of my 6 sheets nor for the 2 criteria search (Product and Batch Code). Also, for accounting purposes I cannot move any data, because other departments reference that same Price.xls workbook. Michelle "Dave F" wrote: You would need to use the VLOOKUP function to do what you're trying to do. However, one suggestion would be to have one workbook rather than two for this task. The reason for this is that the syntax for VLOOKUP formulas is rather complex; it is made more so by having a link to an external workbook. Since you say you're a beginner you may wish to save yourself the headache of auditing VLOOKUPS which reference external workbooks. In any event, this site explains how VLOOKUP works: http://www.techonthenet.com/excel/formulas/vlookup.php Best thing for you to do would go to the site, try the VLOOKUP, and, if you run into problems, reply to this post with the VLOOKUP syntax you are using and someone can help you. Dave -- Brevity is the soul of wit. "MichelleS" wrote: I have two EXCEL 2000 workbooks( Estimates.xls and Prices.xls). Workbook 'Estimates.xls' has 1 worksheet with empty cells (C&D) that need populating if columns A&B are filled in, see below. (snapshot of ESTIMATES.xls) A B C D 1 Product Batch Description Est Price 2 400 01 ____ $____ 3 400 03 ____ $____ 4 600 01 ____ $____ 5 200 02 ____ $____ 6 900 01 ____ $____ 7 900 02 ____ $____ Workbook 'Prices.xls' has 6 worksheets that are lists of different brands with prices used for referencing the 'Estimate.xls' workbook. Its sheets are titled 'Brand 1, Brand 2 thru Brand 6', see below. (snapshot of PRICES.xls, specifically Brand 1 worksheet) A B C D 1 Product Batch Description Est Price 2 200 01 Red $45 3 300 03 Blue Small $75 4 600 01 Orange Med $47 5 900 02 Large $87 6 400 01 Small $96 7 900 01 Medium $15 (PRICES.xls, Brand 2 worksheet) A B C D 1 Product Batch Description Est Price 2 100 01 Green $22 3 800 03 Brown $14 4 400 01 Black $95 5 200 02 XL $67 6 900 03 XXL $98 7 700 02 XXXL $10 (this is a snapshot of PRICES.xls, Brand 3 worksheet) A B C D 1 Product Batch Description Price 2 500 01 Cyan $44 3 400 03 Magenta $29 Worksheets 4 thru 6 are very similar. Any help with this is appreciated. (ps: I'm a beginner so please use details in response) Michelle |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2 Workbooks and 6 Worksheets - Need to Populate 1 Workbook
Are you saying that you want to match on Product and Batch, and can you
guarantee that there are no duplicates of these in your pricing worksheets? In your examples you have 400 01 in two sheets, so would you like to specify the Brand in another column of your Estimates file, or can we say that such duplicates will not occur in the actual Prices file? If you want to perform a lookup over a number of lookup tables, the standard approach is something like: =IF(ISNA(vlookup1),IF(ISNA(vlookup2),IF(ISNA(vlook up3),"missing",vlookup3),vlookup2),vlookup1) This is for 3 tables, but hopefully with the symmetry of the formula you can see how it could be extended to up to 6. I think you may be pushing it with the limit on the number of characters in a formula, so you may like to have a column which looks at 3 sheets and another column which looks at the other three sheets (these could be hidden) and then a final formula which chooses the appropriate value from the two hidden columns (at least one of which will contain "missing" if the entries are unique). However, you want to match on two columns, so VLOOKUP is not appropriate if you can't amend your pricing tables to amalgamate the lookups - you will have to use an INDEX/MATCH/MATCH combination. Hope this helps. Pete MichelleS wrote: Romelsb, Thanks for replying but..................... In all honesty, the 'Descriptions', I have provided you are irrelevant, only cell fillers in place of the true info. The sheet names.....Brand 1, Brand 2, etc, etc. are also irrelevant, only sheet name fillers in place of the true info., So do not focus on those....... "My main interest is in finding a way to automatically poplulate cells from one workbook with 6 different sheets to another workbook with 1 sheet." I have tried the VLOOKUP function below with cntrl+shft+enter, it works for only 1 criteria search not both (product# and batch#). Maybe there is another function INDEX MATCH or something that I should be using......... =IF(OR('[Prices.xls]sheet1'!$A$2:$A$3=C2),VLOOKUP(C2,'[Prices.xls]sheet1'!$A$2:$C$3,3,FALSE),IF(OR('[Prices.xls]sheet2'!$A$2:$A$3=C2),VLOOKUP(C2,'[Prices.xls]sheet2'!$A$2:$C$3,3,FALSE),IF(OR('[Prices.xls]sheet3'!$A$2:$A$3=C2),VLOOKUP(C2,'[Prices.xls]sheet3'!$A$2:$C$3,3,FALSE),IF(OR('[Prices.xls]sheet4'!$A$2:$A$3=C2),VLOOKUP(C2,'[Prices.xls]sheet4'!$A$2:$C$3,3,FALSE),IF(OR('[Prices.xls]sheet5'!$A$2:$A$3=C2),VLOOKUP(C2,'[Prices.xls]sheet5'!$A$2:$C$3,3,FALSE),IF(OR('[Prices.xls]sheet6'!$A$2:$A$3=C2),VLOOKUP(C2,'[Prices.xls]sheet6'!$A$2:$C$3,3,FALSE),"")))))) Again Anyone with experience in this area please assist. Thank you much !! "romelsb" wrote: excuse me Dave...Michelle, From your sheet title: Brand 1 : item 6 : Product 400, Batch 01, Description Small, Est.Price $96-- Brand 2 : item 4 : Product 400, Batch 01, Description Black, Est.Price $95-- Your encoding sheet A & B on Estimates.xls may not be enough if you dont add another column LIKE for Brand.... The BRANDS sheet Description is quite strange- some are by sizes, others are by colors....please check first the 6 Brand Sheet Description and Product coding....more power... "Bright minds are blessed to those who share them.."-rsb. "MichelleS" wrote: Thank you Dave, but I tried the VLOOKUP and it does not work for all of my 6 sheets nor for the 2 criteria search (Product and Batch Code). Also, for accounting purposes I cannot move any data, because other departments reference that same Price.xls workbook. Michelle "Dave F" wrote: You would need to use the VLOOKUP function to do what you're trying to do. However, one suggestion would be to have one workbook rather than two for this task. The reason for this is that the syntax for VLOOKUP formulas is rather complex; it is made more so by having a link to an external workbook. Since you say you're a beginner you may wish to save yourself the headache of auditing VLOOKUPS which reference external workbooks. In any event, this site explains how VLOOKUP works: http://www.techonthenet.com/excel/formulas/vlookup.php Best thing for you to do would go to the site, try the VLOOKUP, and, if you run into problems, reply to this post with the VLOOKUP syntax you are using and someone can help you. Dave -- Brevity is the soul of wit. "MichelleS" wrote: I have two EXCEL 2000 workbooks( Estimates.xls and Prices.xls). Workbook 'Estimates.xls' has 1 worksheet with empty cells (C&D) that need populating if columns A&B are filled in, see below. (snapshot of ESTIMATES.xls) A B C D 1 Product Batch Description Est Price 2 400 01 ____ $____ 3 400 03 ____ $____ 4 600 01 ____ $____ 5 200 02 ____ $____ 6 900 01 ____ $____ 7 900 02 ____ $____ Workbook 'Prices.xls' has 6 worksheets that are lists of different brands with prices used for referencing the 'Estimate.xls' workbook. Its sheets are titled 'Brand 1, Brand 2 thru Brand 6', see below. (snapshot of PRICES.xls, specifically Brand 1 worksheet) A B C D 1 Product Batch Description Est Price 2 200 01 Red $45 3 300 03 Blue Small $75 4 600 01 Orange Med $47 5 900 02 Large $87 6 400 01 Small $96 7 900 01 Medium $15 (PRICES.xls, Brand 2 worksheet) A B C D 1 Product Batch Description Est Price 2 100 01 Green $22 3 800 03 Brown $14 4 400 01 Black $95 5 200 02 XL $67 6 900 03 XXL $98 7 700 02 XXXL $10 (this is a snapshot of PRICES.xls, Brand 3 worksheet) A B C D 1 Product Batch Description Price 2 500 01 Cyan $44 3 400 03 Magenta $29 Worksheets 4 thru 6 are very similar. Any help with this is appreciated. (ps: I'm a beginner so please use details in response) Michelle |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine multiple workbooks into 1 workbook w/ multiple worksheets | Excel Discussion (Misc queries) | |||
Combine multiple workbooks into 1 workbook w/ multiple worksheets | Excel Discussion (Misc queries) | |||
Linking between multiple worksheets, workbooks and columns | Excel Discussion (Misc queries) | |||
Can I split worksheets from one workbook into individual workbooks | Excel Discussion (Misc queries) | |||
Can I tile worksheets within a workbook, like one tiles workbooks. | Excel Discussion (Misc queries) |