M
MichelleS
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 ____ $____
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
(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
(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.
I have tried this VLOOKUP formula below which works for finding the product
codes but not the batch codes; and it only works for the first 2 sheets and
not the final 4 sheets.......HELP !!!
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),""))))))
PLEASE Any help with this is appreciated.
(ps: I'm a beginner so please use details in response)
Michelle
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 ____ $____
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
(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
(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.
I have tried this VLOOKUP formula below which works for finding the product
codes but not the batch codes; and it only works for the first 2 sheets and
not the final 4 sheets.......HELP !!!
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),""))))))
PLEASE Any help with this is appreciated.
(ps: I'm a beginner so please use details in response)
Michelle