How to move data to another worksheet depending on the criteria

[harwookf]

Hi. Is there a way to list, on a separate sheet in the same workbook, only
rows of data with a value in a specific column? I want to list closed leads
and all data in that row) as long as there is a specific value of 100 or 0
in column F. If there is a different value in column F for that lead, then I
don't want it to
appear on this list.
Is there a way to do this with a formula and not a pivot table and
automatically instead of filtering? Ideally, I would like it to add the row
to the bottom of the list.
For example, my colums are (A-I):-
Opportunity ID, Reminder Call, Customer Name, Opportunity, Sales Stage,
Probability, First Name, Last Name and Received Date
Thanks

 

[Earl Kiosterud]

Harwookf,

Take a look at Data - Filter - Advanced filter. It will do exactly as you have asked. You
have to set up a criteria range somewhere on the sheet. Or you can use Autofilter, then
copy/paste the stuff.
--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...

 

[Earl Kiosterud]

I just noticed that you don't want a manual process like filtering. So never mind. It
might be do-able with formulas, but it may be messy. A macro might be the only way.

--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------

 

[harwookf]

OK thanks. I'll try the advanced filter and macro and see how that goes.

 

[Max]

I've just posted a simple, non-array formulas play in your identical posting
in .worksheet.functions. Pl refrain from multi-posting.

 

[harwookf]

Apologies for the duplication.

 

[Brandon]

Would you be able to provide the formula that you posted? I have searched
for the other post unsuccessfully.

 

[Max]

Brandon, here it is:
----------------
Here's a formulas play which can deliver the automation you seek

Assume source data is in a sheet: x, cols A to I,
data from row2 down, with key col F & criteria values: 0 or 100

In another sheet: y (say),

Place in A2:
=IF(x!F2="","",IF(OR(x!F2={0,100}),ROW(),""))
Leave A1 blank

Put in B2:
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(x!A:A,SMALL($A:$A,ROWS($1:1))))
Copy B2 to K2. Select A2:J2, copy down to cover the max expected extent of
source data in x, say down to J500? Hide away col A. Cols B to J will return
the required results, ie only the lines where col F contains either: 0 or
100, with all lines neatly bunched at the top. As inputs are made in x, y
will automatically display the required lines.
-------------------------

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik