what formula do I use to pull rows containing a number?

  • Thread starter Thread starter Alexis
  • Start date Start date
A

Alexis

I have an order form that in the last column my sales guys put a 1 if they
wish to order something. I would like to have a formula set up that will
basically do the same as Data, Filter, Auto Filter, Custom Dropdown, Greater
then or equal to 1... Help me
 
One easy formulas play to get it going over there ..

Assume source data in Sheet1's col A to D,
with col D = key col (where the quantities will be entered)
Assume data is from row 4 down

In Sheet2 (your "Pullout" order summary),
In A2: =IF(Sheet1!D4>0,ROW(),"")
Leave A1 blank

In B2:
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(Sheet1!A:A,SMALL($A:$A,ROWS($1:1))+2))
Copy B2 to D2. Select A2:D2, fill down to cover the max expected extent of
data, say down to D200?. Hide/minimize col A. Cols B to D will return the
exact results that you seek, all neatly bunched at the top.

P/s: The "+2" in B2's formula is just an arithmetic adjustment as data in
Sheet1 starts in row 4 down, while the criteria col A in Sheet2 starts in row
2 down. Adapt to suit.
 
Back
Top