Max
Thu Jul 08 05:41:15 CDT 2004
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
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----
"daljit >" <<daljit.192co2@excelforum-nospam.com> wrote in message
news:daljit.192co2@excelforum-nospam.com...
> I have created a workbook with two sheets in it. I enter all my orders
> in sheet one and the sheet two totals them. The column on my sheets are
> 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 this
> ??.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 at
> the style numbers in the sheet two and insert it there if does not find
> it there.
>
> Please help
>
> Thank You in advance
>
> Dale
>
>
> ---
> Message posted from
http://www.ExcelForum.com/
>