I have a list of names with check boxes next to it. I want to create a list
of the names on the same sheet of all checked boxes without blank rows. I
can use IF statement to assign to a single cell but this results in blank
rows of unchecked item. Does anyone know how I can do this? Please help!

Are the check boxes linked to cells?

Let's assume the names are in the range A1:A10.
Check boxes in the range B1:B10 and B1:B10 are the linked cells.

Enter this array formula** in D1 and copy down until you get blanks:

=IF(ROWS(D$1:D1)<=SUM(--B$1:B$10),INDEX(A$1:A$10,SMALL(IF(B$1:B$10,ROW(A$1 :A$10)),ROWS(D$1:D1))-ROW(A$1)+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"boatatsea" wrote in message
...
I have a list of names with check boxes next to it. I want to create a
list
of the names on the same sheet of all checked boxes without blank rows. I
can use IF statement to assign to a single cell but this results in blank
rows of unchecked item. Does anyone know how I can do this? Please help!

Thanks!! Exactly what I needed!

"T. Valko" wrote:

Are the check boxes linked to cells?

Let's assume the names are in the range A1:A10.
Check boxes in the range B1:B10 and B1:B10 are the linked cells.

Enter this array formula** in D1 and copy down until you get blanks:

=IF(ROWS(D$1:D1)<=SUM(--B$1:B$10),INDEX(A$1:A$10,SMALL(IF(B$1:B$10,ROW(A$1 :A$10)),ROWS(D$1:D1))-ROW(A$1)+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"boatatsea" wrote in message
...
I have a list of names with check boxes next to it. I want to create a
list
of the names on the same sheet of all checked boxes without blank rows. I
can use IF statement to assign to a single cell but this results in blank
rows of unchecked item. Does anyone know how I can do this? Please help!

Actually, I'm still having problems. what if the names are in the range of
A4:A5, D4:D5,G4:G5,J4:J5? and the check boxes in the columns next to the
names? How do you tweak this formula?


"T. Valko" wrote:

Are the check boxes linked to cells?

Let's assume the names are in the range A1:A10.
Check boxes in the range B1:B10 and B1:B10 are the linked cells.

Enter this array formula** in D1 and copy down until you get blanks:

=IF(ROWS(D$1:D1)<=SUM(--B$1:B$10),INDEX(A$1:A$10,SMALL(IF(B$1:B$10,ROW(A$1 :A$10)),ROWS(D$1:D1))-ROW(A$1)+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"boatatsea" wrote in message
...
I have a list of names with check boxes next to it. I want to create a
list
of the names on the same sheet of all checked boxes without blank rows. I
can use IF statement to assign to a single cell but this results in blank
rows of unchecked item. Does anyone know how I can do this? Please help!

Well, that changes things considerably!

Since the data in question is not in a contiguous range you'd have to use a
intermediate list and then extract the data from this intermediate list.

Assuming the linked cells are B4:B5, E4:E5, H4:H5, K4:K5

Create the intermediate list:

A10: =IF(B4,A4,"")
A11: =IF(B5,A5,"")
A12: =IF(E4,D4,"")
A13: =IF(E5,D5,"")
A14: =IF(H4,G4,"")
A15: =IF(H5,G5,"")
A16: =IF(K4,J4,"")
A17: =IF(K5,J5,"")

Then, to get the names in a contiguous list, array entered in B10 and copied
down to B17:

=IF(ROWS(B$10:B10)<=COUNTIF(A$10:A$17,"?*"),INDEX( A$10:A$17,SMALL(IF(A$10:A$17<"",ROW(A$10:A$17)),R OWS(A$10:A10))-ROW(A$10)+1),"")

--
Biff
Microsoft Excel MVP


"boatatsea" wrote in message
...
Actually, I'm still having problems. what if the names are in the range
of
A4:A5, D4:D5,G4:G5,J4:J5? and the check boxes in the columns next to the
names? How do you tweak this formula?


"T. Valko" wrote:

Are the check boxes linked to cells?

Let's assume the names are in the range A1:A10.
Check boxes in the range B1:B10 and B1:B10 are the linked cells.

Enter this array formula** in D1 and copy down until you get blanks:

=IF(ROWS(D$1:D1)<=SUM(--B$1:B$10),INDEX(A$1:A$10,SMALL(IF(B$1:B$10,ROW(A$1 :A$10)),ROWS(D$1:D1))-ROW(A$1)+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"boatatsea" wrote in message
...
I have a list of names with check boxes next to it. I want to create a
list
of the names on the same sheet of all checked boxes without blank rows.
I
can use IF statement to assign to a single cell but this results in
blank
rows of unchecked item. Does anyone know how I can do this? Please
help!