move data to another sheet depending on criteria
 

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? 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

move data to another sheet depending on criteria
 

Use AutoFilter:

Set up AutoFilter on the original data and select 100 for the dropdown on
column F.

Copy the visible rows and paste elsewhere.

Repeat for 0
--
Gary''s Student - gsnu200742


"harwookf" wrote:

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? 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

move data to another sheet depending on criteria
 

This is the way I have been doing it, but was wondering if it possible to do
it automatically?

"Gary''s Student" wrote:

Use AutoFilter:

Set up AutoFilter on the original data and select 100 for the dropdown on
column F.

Copy the visible rows and paste elsewhere.

Repeat for 0
--
Gary''s Student - gsnu200742


"harwookf" wrote:

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? 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

move data to another sheet depending on criteria
 

You could use a macro to automate the process. The macro would mimic what
you are currently doing manually.
--
Gary''s Student - gsnu200742


"harwookf" wrote:

This is the way I have been doing it, but was wondering if it possible to do
it automatically?

"Gary''s Student" wrote:

Use AutoFilter:

Set up AutoFilter on the original data and select 100 for the dropdown on
column F.

Copy the visible rows and paste elsewhere.

Repeat for 0
--
Gary''s Student - gsnu200742


"harwookf" wrote:

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? 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

move data to another sheet depending on criteria
 

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
xdemechanik
---
"harwookf" wrote:
This is the way I have been doing it, but was wondering if it possible to do
it automatically?

move data to another sheet depending on criteria
 

Many thanks for this. It works perfectly, just as I wanted.

"Max" wrote:

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
xdemechanik
---
"harwookf" wrote:
This is the way I have been doing it, but was wondering if it possible to do
it automatically?

move data to another sheet depending on criteria
 

welcome, glad it helped.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"harwookf" wrote in message
...
Many thanks for this. It works perfectly, just as I wanted.

move data to another sheet depending on criteria
 

I completed my spreadsheet using the formulas given, however, I was wondering
if it is possible to delete the data automatically from the original sheet
once it has been moved to the second sheet. Hiding the data would work so
long as it is an automatic process.

Many thanks in advance.


"harwookf" wrote:

Many thanks for this. It works perfectly, just as I wanted.

"Max" wrote:

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
xdemechanik
---
"harwookf" wrote:
This is the way I have been doing it, but was wondering if it possible to do
it automatically?

move data to another sheet depending on criteria
 

Pl see response in your new post
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---