Create sublist in new worksheet

[Madeline Koch]

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

 

[Stefi]

Make sure you have a header row!
Select A1!
Data>Filters>Autofilter
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:

 

[Max]

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,SMALL(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.

 

[Madeline Koch]

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,SMALL(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

 

[Max]

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,SMALL(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,SMALL($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/

 

[Madeline Koch]

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

 

[Max]

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

 

[Ashish Mathur]

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]

Max, thanks so much for setting that up. I transferred it to my live file,
and at first it was a little wonky. The only change I made was to change the
column identifiers in the Index formula to correspond to my real columns. So
far so good.

But at first it was importing some of the rows that had n instead of y
source field.

I tried again, and it worked. But things weren't quite in the right
alphabetical order, so I tried resorted the source worksheet.

That did rearrange things, but for some reason it stops at the 37th row,
even though there are 143 in the actual source worksheet -- and 52 rows with
a y in them!

Thanks for your help.
Madeline

 

[Max]

I tried again, and it worked. But things weren't quite in the right

alphabetical order, so I tried resorted the source worksheet.

The extracted lines would appear in the same relative order as they are
within the source. There's no alphabetic sorting.

That did rearrange things, but for some reason it stops at the 37th row,
even though there are 143 in the actual source worksheet -- and 52 rows
with
a y in them!

I think you might have messed the criteria col up when you re-sorted the
source sheet. Suggest that you restore the set up like this. Re-fill the
criteria col from the top cell (do ensure that the top cell's formula is
intact, ie pointing to the 1st data cell in the source). Drag down to copy
again all the way to cover the max expected extent of the source (do ensure
that the copy down extent does indeed cover the full source data extent, or
beyond)

 

[Madeline Koch]

I think you might have messed the criteria col up when you re-sorted the
source sheet. Suggest that you restore the set up like this. Re-fill the
criteria col from the top cell (do ensure that the top cell's formula is
intact, ie pointing to the 1st data cell in the source). Drag down to copy
again all the way to cover the max expected extent of the source (do ensure
that the copy down extent does indeed cover the full source data extent, or
beyond)

I tried starting again from scratch. Now going wonky after row 23 (the
source worksheet has 143 rows). This is what I've got (note that I don't
need all the columns from the source worksheet):

ROW 1
Column A:
=IF(invitations!B1="y",ROW(),"")

Column B:
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(invitations!C:C,SMALL($A:$A,ROWS($1:1))
))

Column C:
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(invitations!D,SMALL($A:$A,ROWS($1:1))
))

ROW 25
A
=IF(invitations!B25="y",ROW(),"")

B
=IF(ROWS($1:25)>COUNT($A:$A),"",INDEX(invitations!C:C,SMALL($A:$A,ROWS($1:25
))))

C
=IF(ROWS($1:25)>COUNT($A:$A),"",INDEX(invitations!D,SMALL($A:$A,ROWS($1:25
))))

In Column A, stuff is working -- the source-worksheet rows that have a y in
the column B have the row number carried over. But no other info from that
row makes it. Also there seems to be the "formula refers to empty cell"
error everywhere except in the cells that contain data.

I'm stumped again!
Thanks,
Madeline