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

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...
-----------------------------------------------------------------------
"harwookf" wrote in message
...
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

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...
-----------------------------------------------------------------------
"Earl Kiosterud" wrote in message
...
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...
-----------------------------------------------------------------------
"harwookf" wrote in message
...
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

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

"Earl Kiosterud" wrote:

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...
-----------------------------------------------------------------------
"Earl Kiosterud" wrote in message
...
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...
-----------------------------------------------------------------------
"harwookf" wrote in message
...
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

I've just posted a simple, non-array formulas play in your identical posting
in .worksheet.functions. Pl refrain from multi-posting.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Apologies for the duplication.

"Max" wrote:

I've just posted a simple, non-array formulas play in your identical posting
in .worksheet.functions. Pl refrain from multi-posting.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

"Max" wrote:

I've just posted a simple, non-array formulas play in your identical posting
in .worksheet.functions. Pl refrain from multi-posting.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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
---
"Brandon" wrote:
Would you be able to provide the formula that you posted? I have searched
for the other post unsuccessfully.