lookup in one sheet and insert in second if not found

[daljit]

I have created a workbook with two sheets in it. I enter all my order
in sheet one and the sheet two totals them. The column on my sheets ar
as follow….

Sheet one
STYLE SMALL MEDIUM LARGE
100 1 1 1
101 1 1 1
100 1 1 1
102

Sheet two
STYLE SMALL MEDIUM LARGE
100 2 2 2
101 1 1 1

I have used the SUMIF command in the second sheet, which is correct
Therefore my totals are correct. The only problem I am having is thi
…….that I have to enter the style numbers in the second sheet as well.
What I want to do is, to have some kind of look up function some where
So when I enter the style number in the sheet one, it should look a
the style numbers in the sheet two and insert it there if does not fin
it there.

Please help

Thank You in advance

Dal

 

[Max]

One possible approach ..

In Sheet1
-------------
You have in cols A to D, data from row2 down

STYLE SMALL MEDIUM LARGE
100 1 1 1
101 1 1 1
100 1 1 1
102 1 1 1
103 1 1 1
104 1 1 1
103 1 1 1

In an empty col to the right, say in col F

Put in F2: =IF(COUNTIF($A$2:A2,A2)-1=0,ROW(),"")

Copy F2 down a "safe" max number of rows that would be expected
say, to F100

(This sets it up for us to extract
the unique "Styles" in col A into Sheet2's col A)

In Sheet2
-------------
Put in A2:

=IF(ISERROR(MATCH(SMALL(Sheet1!F:F,ROW()-1),Sheet1!F:F,0)),"",OFFSET(Sheet1!
$A$1,MATCH(SMALL(Sheet1!F:F,ROW()-1),Sheet1!F:F,0)-1,))

Copy A2 down to A100
(to cover the same number of rows as in col F of Sheet1)

Col A will return all the unique "Styles" from the list in Sheet1

For the sample data in Sheet1, Sheet2 will show:

STYLE SMALL MEDIUM LARGE
100 1 1 1
101 1 1 1
102 1 1 1
103 1 1 1
104 1 1 1

 

[Max]

Just a tiny clarification ..

The same structure is assumed in Sheet2,
i.e. headers in row1, data from row2 down