Roel,

On sheet 2 in cell A1 to D1, enter 4, 6, 27, 28 (These are the column numbers of the data you want
to "extract" Then in E1, enter 50000.

In cell A2:D2, enter your headings "Vendor", "Value", "Month", "Year"
In cell A3, array enter (enter using Ctrl-Shift-Enter)

=INDIRECT("'Data'!" & ADDRESS(LARGE((Data!$F$2:$F$1000=$E$1)
*ROW(Data!$D$2:$D$1000),COUNTIF(Data!F$2:$F$1000," =" & $E$1) +ROW($A$3)-ROW()),A$1))
(Change Data to the actual sheet name of what you describe as sheet 1, in all four places.... And
change the three instances of 1000 to the actual last row number of your data sheet or, at least, to
a larger number.)

If you have entered this correctly, Excel will enclose the formula in { } - DO NOT type the { }
yourself....

Finally, copy A3 to B3:D3, then copy A3:D3 down for as many rows as you need: you will get error
values when you have extracted all the data.

HTH,
Bernie
MS Excel MVP


"Roel Broos" <Roel wrote in message
...
Hello everyone,

I am a bit stuck on this problem and I don't know if this is possible to do
in Excel 2007...

I have multiple worksheets in a workbook. first worksheet is raw data,
second is a statistics worksheet.
On sheet 1 I have the following:

Column D: Vendor Name
Column F: Order value
Column AA: Month number (as result of a =MONTH function)
Column AB: Year number (as a result of =YEAR function)
Column AC: shows a "1" if order value is bigger than 50.000 (as a result of
=IF function)

What I want do do on sheet 2 is display all orders bigger than 50k including
Vendor name and amount , example:

"Vendor Name" "Value" "Month" "Year"
"Vendor Name" "Value" "Month" "Year"
"Vendor Name" "Value" "Month" "Year"
"Vendor Name" "Value" "Month" "Year"
etc.

The number of orders varies over time offcourse. I do not want to use a
pivot table, as more people have to use this sheet and are not up to using
pivot tables....

Does anyone has a suggestion ?
Thanks in advance!

Roel.