I have a master table and if one of the columns has a "No" in it then I would
like the information in that row to automatically be copied into another
table with the same heading information. Very similar to using a filter, but
actually copying the information.
For example...i have this table


Name RSVP Date Received
Jane Doe Yes 1/30/2008
John Doe No
Jill Doe Yes 2/5/2008
Jack Doe No

And I want it to automatically put it in something like this on a different
worksheet:

Name RSVP Date Received
John Doe No
Jack Doe No


Thanks in advance for the help!

Here's a relatively simple construct which gets you the results w/o fuss

Assume source table in sheet: x, cols A to C,
data from row 2 down, with key col B = RSVP

In another sheet,

In A2: =IF(x!B2="No",ROW(),"")
Leave A1 blank

In B2:
=IF(ROWS($1:1)COUNT($A:$A),"",INDEX(x!A:A,SMALL($ A:$A,ROWS($1:1))))
Copy B2 to C2. Select A2:C2, fill down to cover the max expected extent of
data in x. This will return what you're after, with all lines neatly bunched
at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Lee" wrote:
I have a master table and if one of the columns has a "No" in it then I would
like the information in that row to automatically be copied into another
table with the same heading information. Very similar to using a filter, but
actually copying the information.
For example...i have this table


Name RSVP Date Received
Jane Doe Yes 1/30/2008
John Doe No
Jill Doe Yes 2/5/2008
Jack Doe No

And I want it to automatically put it in something like this on a different
worksheet:

Name RSVP Date Received
John Doe No
Jack Doe No


Thanks in advance for the help!

Max...Thanks that worked great when I put the data on a chart on a different
sheet.

What if I want to put it in a table that is on the same sheet just 15-20
rows below the original table. I have tried using a similar formula but
nothing seems to work. I can't figure out what I need to change. Thanks so
much!



"Max" wrote:

Here's a relatively simple construct which gets you the results w/o fuss

Assume source table in sheet: x, cols A to C,
data from row 2 down, with key col B = RSVP

In another sheet,

In A2: =IF(x!B2="No",ROW(),"")
Leave A1 blank

In B2:
=IF(ROWS($1:1)COUNT($A:$A),"",INDEX(x!A:A,SMALL($ A:$A,ROWS($1:1))))
Copy B2 to C2. Select A2:C2, fill down to cover the max expected extent of
data in x. This will return what you're after, with all lines neatly bunched
at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Lee" wrote:
I have a master table and if one of the columns has a "No" in it then I would
like the information in that row to automatically be copied into another
table with the same heading information. Very similar to using a filter, but
actually copying the information.
For example...i have this table


Name RSVP Date Received
Jane Doe Yes 1/30/2008
John Doe No
Jill Doe Yes 2/5/2008
Jack Doe No

And I want it to automatically put it in something like this on a different
worksheet:

Name RSVP Date Received
John Doe No
Jack Doe No


Thanks in advance for the help!

What if I want to put it in a table that is on the same sheet just 15-20
rows below the original table.

It becomes less neat, but you could adapt it like this
Assume source data is within A2:C30, key col B = RSVP (as before)
In A50: =IF(B2="No",ROWS($1:1),"")
In B50:
=IF(ROWS($1:1)COUNT($A$50:$A$78),"",INDEX(A$2:A$3 0,SMALL($A$50:$A$78,ROWS($1:1))))
Copy B50 to C50. Select A50:C50, fill down to C78. Cols B & C returns source
lines where RSVP = "No"
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Lee" wrote:
Max...Thanks that worked great when I put the data on a chart on a different
sheet.

What if I want to put it in a table that is on the same sheet just 15-20
rows below the original table. I have tried using a similar formula but
nothing seems to work. I can't figure out what I need to change. Thanks so
much!