I have one worksheet of all the people invited to an event. One column
indicates y for they're coming, n for they're not and blank if they have not
yet replied.

I want to set up a separate worksheet that lists *only* the acceptances. In
other words, I want a separate worksheet that extracts the rows of data that
contain y in column B but none of the other rows.

Any advice?
Madeline in Toronto

Make sure you have a header row!
Select A1!
DataFiltersAutofilter
Set Autofilter to "y" in column of comings!
Select all visible rows (click on row1 header and drag it down to the last
visible row)!
Copy/Paste into a 2nd sheet!

--
Regards!
Stefi



€žMadeline Koch€� ezt Ã*rta:

I have one worksheet of all the people invited to an event. One column
indicates y for they're coming, n for they're not and blank if they have not
yet replied.

I want to set up a separate worksheet that lists *only* the acceptances. In
other words, I want a separate worksheet that extracts the rows of data that
contain y in column B but none of the other rows.

Any advice?
Madeline in Toronto





.

A simple formulas option to set it up dynamic in another sheet ..
Assume source data in Sheet1, cols A & B, data in row2 down,
where col A = Names, col B = Status (eg: y - for accepted)
In another sheet,
In A2: =IF(Sheet1!B2="y",ROW(),"")
In B2: =IF(ROWS($1:1)COUNT(A:A),"",INDEX(Sheet1!A:A,SMAL L(A:A,ROWS($1:1))))
Copy A2:B2 down to cover the max expected extent of source data, eg down to
B200? Hide/minimize col A. Col B will auto-return the list of source names
(those with "y") as the source data changes, with all results neatly bunched
at the top.
--
Max
Singapore
------
"Madeline Koch" wrote in message
...
I have one worksheet of all the people invited to an event. One column
indicates y for they're coming, n for they're not and blank if they have
not
yet replied.

I want to set up a separate worksheet that lists *only* the acceptances.
In
other words, I want a separate worksheet that extracts the rows of data
that
contain y in column B but none of the other rows

Thanks, Max. I wanted it to be a dynamic list so Stefi's suggestion was not
quite right for my purposes.

I kind of got this to work, but I don't know how to get the list of the Ys
all at the top.

If I sent you a dummy file you could see what I've done (it's very difficult
to explain here, but I could try...!)

Cheers,
Madeline


A simple formulas option to set it up dynamic in another sheet ..
Assume source data in Sheet1, cols A & B, data in row2 down,
where col A = Names, col B = Status (eg: y - for accepted)
In another sheet,
In A2: =IF(Sheet1!B2="y",ROW(),"")
In B2: =IF(ROWS($1:1)COUNT(A:A),"",INDEX(Sheet1!A:A,SMAL L(A:A,ROWS($1:1))))
Copy A2:B2 down to cover the max expected extent of source data, eg down to
B200? Hide/minimize col A. Col B will auto-return the list of source names
(those with "y") as the source data changes, with all results neatly bunched
at the top.
T

Probably just need to make an arithmetic adjustment to the 2nd formula
Eg if your source data starts in row 4 down in Sheet1
In another sheet,
In A2: =IF(Sheet1!B4="y",ROW(),"")
In B2:
=IF(ROWS($1:1)COUNT(A:A),"",INDEX(Sheet1!A:A,SMAL L(A:A,ROWS($1:1))+2))
The "+2" is the required adjustment

And if you have more than 1 source col to return from Sheet1,
just fix the point to col A: $A:$A in the 2nd formula
ie use in B2:
=IF(ROWS($1:1)COUNT($A:$A),"",INDEX(Sheet1!A:A,SM ALL($A:$A,ROWS($1:1))+2))
Copy B2 across by as many cols as required. Then select n copy A2:Z2 (say)
down to cover the max expected extent of source data, eg down to Z200?
Hide/minimize col A. Cols B to Z will auto-return the required list of
source lines satisfying the criteria as the source data changes, with all
results neatly packed at the top

Try the above first. It should clear it up and get it going sweetly for you
You can use this link to upload a sample
http://cjoint.com/

--
Max
Singapore
------
"Madeline Koch" wrote in message
...
Thanks, Max. I wanted it to be a dynamic list so Stefi's suggestion was
not
quite right for my purposes.

I kind of got this to work, but I don't know how to get the list of the Ys
all at the top.

If I sent you a dummy file you could see what I've done (it's very
difficult
to explain here, but I could try...!)

Cheers,
Madeline

Sorry to be dense, Max, but I do have all the data coming into the second
worksheet just fine, but it leaves them in the same order as in the
original. The rows that don't correspond to the criteria are blank. So it
ends up with:
Row 2: y | firstname | lastname
Row 3: blank
Row 4: y | firstname | lastname
Row 5: y | firstname | lastname

I got this to work using the following formulae:
Column A: =IF(invitations!B2="y",invitations!B2,"")
Column B: =IF(invitations!B2="y",invitations!C2,"")
Column C: =IF(invitations!B2="y",invitations!D2,"")

I don't understand your second formula and where it should go!
Thanks again.
Madeline

Probably just need to make an arithmetic adjustment to the 2nd formula
Eg if your source data starts in row 4 down in Sheet1
In another sheet,
In A2: =IF(Sheet1!B4="y",ROW(),"")
In B2:
=IF(ROWS($1:1)COUNT(A:A),"",INDEX(Sheet1!A:A,SMAL L(A:A,ROWS($1:1))+2))
The "+2" is the required adjustment

And if you have more than 1 source col to return from Sheet1,
just fix the point to col A: $A:$A in the 2nd formula
ie use in B2:
=IF(ROWS($1:1)COUNT($A:$A),"",INDEX(Sheet1!A:A,SM ALL($A:$A,ROWS($1:1))+2))
Copy B2 across by as many cols as required. Then select n copy A2:Z2 (say)
down to cover the max expected extent of source data, eg down to Z200?
Hide/minimize col A. Cols B to Z will auto-return the required list of
source lines satisfying the criteria as the source data changes, with all
results neatly packed at the top

Try the above first. It should clear it up and get it going sweetly for you
You can use this link to upload a sample
http://cjoint.com/

--
Max
Singapore
------
"Madeline Koch" wrote in message
...
Thanks, Max. I wanted it to be a dynamic list so Stefi's suggestion was
not
quite right for my purposes.

I kind of got this to work, but I don't know how to get the list of the Ys
all at the top.

If I sent you a dummy file you could see what I've done (it's very
difficult
to explain here, but I could try...!)

Cheers,
Madeline

The 1st formula is the criteria col to flag which lines satisfy (it isn't
meant to extract)
The 2nd formula is the extraction guy. He will read the flags in the
criteria col, then extract it from the source with all lines extracted
appearing neatly packed up at the top

Here's a working sample based on your set-up for easy ref:
http://cjoint.com/?bccoQ60qyl

--
Max
Singapore
"Madeline Koch" wrote in message
...
Sorry to be dense, Max, but I do have all the data coming into the second
worksheet just fine, but it leaves them in the same order as in the
original. The rows that don't correspond to the criteria are blank. So it
ends up with:
Row 2: y | firstname | lastname
Row 3: blank
Row 4: y | firstname | lastname
Row 5: y | firstname | lastname

I got this to work using the following formulae:
Column A: =IF(invitations!B2="y",invitations!B2,"")
Column B: =IF(invitations!B2="y",invitations!C2,"")
Column C: =IF(invitations!B2="y",invitations!D2,"")

I don't understand your second formula and where it should go!
Thanks again.
Madeline

Hi,

Advanced filters can be made dynamic through a simple code. When you will
click on a button, the result of the advanced filter would update

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Madeline Koch" wrote in message
...
Thanks, Max. I wanted it to be a dynamic list so Stefi's suggestion was
not
quite right for my purposes.

I kind of got this to work, but I don't know how to get the list of the Ys
all at the top.

If I sent you a dummy file you could see what I've done (it's very
difficult
to explain here, but I could try...!)

Cheers,
Madeline


A simple formulas option to set it up dynamic in another sheet ..
Assume source data in Sheet1, cols A & B, data in row2 down,
where col A = Names, col B = Status (eg: y - for accepted)
In another sheet,
In A2: =IF(Sheet1!B2="y",ROW(),"")
In B2:
=IF(ROWS($1:1)COUNT(A:A),"",INDEX(Sheet1!A:A,SMAL L(A:A,ROWS($1:1))))
Copy A2:B2 down to cover the max expected extent of source data, eg down
to
B200? Hide/minimize col A. Col B will auto-return the list of source
names
(those with "y") as the source data changes, with all results neatly
bunched
at the top.
T