Excel check box making list

  • Thread starter Thread starter boatatsea
  • Start date Start date
B

boatatsea

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)
 
Thanks!! Exactly what I needed!

T. Valko said:
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)
Click to expand...
 
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?
 
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)),ROWS(A$10:A10))-ROW(A$10)+1),"")
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

check box 2
Check Boxes 1
Sum of check boxes that have been checked 2
VBA to automatically cut, paste entire row based on date input and delete row 0
Copying a column list from one sheet to another and repeating??? 18
Protect and Edit Object 1
Remove blank cells from data validation drop down box 3
I want to make One Cell mandatory if another cell has a specific value , can anyone help me? 0

Back
Top