I have data in a4:k27.
In Col B I have a flag set (called Active) to track active deliveries.
Not all rows have this flag but 11 rows always have this flag.
How to I extract only those rows with the 'active' flag and show in
cols B:G on Sheet1 only. The end result is that sheet 1 will show 11
active deliveries and various details.
Can it be done with a vlookup statement?

Thanks
Tony

No, you would need an array formula. Asusme the sheet containing the Active
entry is Sheet1

=Small(If(Sheet2!$B$4:$B$27="Active",row($B$4:$B$2 7)),row(A1))

if placed in a cell on Sheet1 and enter it with Ctrl+shift+Enter rather than
just enter (so it is treated as an array formula), then drag fill down the
column, it will return the row numbers on sheet2 that contain the word
ACTIVE in column B.

You can use this as the basis for retriving the data. Assume you want the
sheet2 column A values in column of sheet1, starting in B2. In B2 you would
put

=Offset(Sheet2!$A$1,Small(If(Sheet2!$B$4:$B$27="Ac tive",row($B$4:$B$27)),row
(A1))-1,0)

Entered with Ctrl+shift+enter

then drag fill this down the column until you start getting errors.

Adjust the formula to pull back other columns by changing the 0 in the last
argument. 0 is column A (an offset of 0), 3 would be column C and so forth.

--
Regards,
Tom Ogilvy


wrote in message
oups.com...
I have data in a4:k27.
In Col B I have a flag set (called Active) to track active deliveries.
Not all rows have this flag but 11 rows always have this flag.
How to I extract only those rows with the 'active' flag and show in
cols B:G on Sheet1 only. The end result is that sheet 1 will show 11
active deliveries and various details.
Can it be done with a vlookup statement?

Thanks
Tony

Tom,

This is excellent.
Expertly worked out and exactly what I needed.
Thanks also for the instructions

Regards
Tony

use the advanced filter under the Data / Filter menu item

" wrote:

I have data in a4:k27.
In Col B I have a flag set (called Active) to track active deliveries.
Not all rows have this flag but 11 rows always have this flag.
How to I extract only those rows with the 'active' flag and show in
cols B:G on Sheet1 only. The end result is that sheet 1 will show 11
active deliveries and various details.
Can it be done with a vlookup statement?

Thanks
Tony