Thanks Pete for replying to my message, but can you do me a favor and write
out the "=IF(ISNA" formula using the actual cells in my example
"IF(ISNA(VLOOKUP(A1??)", only because I'm not that experienced with this
formula? Thank you again.

MichelleS

"Pete_UK" wrote:

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