Choosing Data and skipping other

I wish to have Excel choos items from another worksheet according to the
criteria I set (ie: if =1, then choose ... but skip if not = 1 and select the
next item = to 1 ...) I can set this up so it replicates the other worksheet
- but I end up with a lot of blank lines - I don't want the lines blank - but
only have lines with information = to my querry.

Thanks,
David

One way using non-array formulas ..

Demo file available at: http://www.savefile.com/files/4090857
Choosing_Data_and_skipping_other_DavidBr318_wksht. xls

Assume you have in Sheet1, cols A & B, data from row2 down, where the key
col is col B

Staff Key col
Name1 2
Name2 1
Name3 2
Name4 1
Name5 1

Use an empty col to the right, say col E ?
Put in E2: =IF(B2="","",IF(B2=1,ROW(),""))
Copy E2 down to say, E10, to cover the max expected data range
(Leave E1 empty)

(Adapt the criteria to suit in col E ..)

In Sheet2
--------
Paste the same headers into A1:B1 : Staff, Key col

Put in A2:
=IF(ISERROR(SMALL(Sheet1!$E:$E,ROWS($A$1:A1))),"",
INDEX(Sheet1!A:A,MATCH(
SMALL(Sheet1!$E:$E,ROWS($A$1:A1)),Sheet1!$E:$E,0)) )

Copy A2 across to B2, fill down to B10
(cover the same range size as done in col E in Sheet1)

Sheet2 will return only the lines satisfying the criteria (i.e. those = 1 in
the key col in Sheet1), all neatly bunched at the top, viz.:

Staff Key col
Name2 1
Name4 1
Name5 1
(blank rows below)

Adapt to suit ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"DavidBr318" wrote in message
...
I wish to have Excel choos items from another worksheet according to the
criteria I set (ie: if =1, then choose ... but skip if not = 1 and select
the
next item = to 1 ...) I can set this up so it replicates the other
worksheet
- but I end up with a lot of blank lines - I don't want the lines blank -
but
only have lines with information = to my querry.

Thanks,
David

On Fri, 14 Oct 2005 02:15:04 -0700, "DavidBr318"
wrote:

I wish to have Excel choos items from another worksheet according to the
criteria I set (ie: if =1, then choose ... but skip if not = 1 and select the
next item = to 1 ...) I can set this up so it replicates the other worksheet
- but I end up with a lot of blank lines - I don't want the lines blank - but
only have lines with information = to my querry.

Thanks,
David

Have you tried Data Filter Advanced?

Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________