Help with checkboxes & data

**Jen L** · 04-27-2012, 09:36 AM

Hello! I have a mailing list with checkboxes beside the names so that employees can check off who they want mailings sent to. There are over 300 names on the list. Is there a way to easily create a new list of only the names that are checked? I'm attaching the Excel file with names/addresses removed. Thank you!Mailing List for Excel Forum.xlsm

**jeffreybrown** · 04-27-2012, 09:40 AM

Hi Jen,

First step would be to link each one of these checkboxes to a cell which would return a TRUE or FALSE.

For me, I would banish the check boxes and go with a double click method which would add a check to the cell using the Marlett font.

Is this something you could work with?

**Jen L** · 04-27-2012, 11:10 AM

Thank you for responding to my post! My boss likes the checkboxes, so I have to stick with those. I'm not very experienced with Excel -- could you give me more info on how to link each checkbox with a cell to return TRUE or FALSE? Can those cells that the checkboxes are linked to be in a different worksheet? Thanks again!

**jeffreybrown** · 04-27-2012, 11:24 AM

Bosses...don't you love them.

The only further comment I will make about the double click simulated checkbox method (lower case "a" formatted with Marlett font) over the checkbox, as soon as you start moving stuff around on the worksheet or add/delete rows, everything will go wonky. Not to mention you are going to have to format all 300 checkboxes one by one. I suppose this could be done with VBA, but I have never undertaken this nor do I really want to when I can go with the double click method.

Anyway, here are the steps.
Right click on the check box
Format Control
Contol Tab
Cell Link: >> add a cell reference

Now that cell you referenced will toggle between TRUE and FALSE as you toggle the checkbox.

As far as the reference on another sheet, looks like this can be done.

Did you see the sample attachment?

**ChemistB** · 04-27-2012, 11:30 AM

I would suggest that you link to Column A (same row) and then hide column A (insert an additional column if it's not esthetically pleasing). Also, if you plan on protecting the sheet, the cells in column A will need to be unlocked. Once that is done, you will be able to use the values in column A to move (or not move) email addresses to a new sheet.

**Jen L** · 04-27-2012, 11:46 AM

Thanks again to you both. I did see the sample attachment. I feel a little stupid; I still don't really understand how this is going to come up with a list of addresses depending on which boxes are checked, but I will work on it and hopefully figure it out. Excel is my weak point! It makes no sense to me.

**Jen L** · 04-27-2012, 11:52 AM

OK -- I understand now... Just a little slow over here!

Combining both of your advice, I have my answer! Thanks again!

**jeffreybrown** · 04-27-2012, 11:54 AM

With the attached example, enable macros and in column A double click any cell between A2:A21.

Once you double click a check will appear (or disappear if you double click again) and in P1 there is a formula to count how many cells in A2:A21 are checked.

Then in E2:E21 the list will automattically be built based on the checks (another formula in these cells).

A2:A21 is a named range >> Ckboxes