Excel 97 Check Boxes

By jmbarnes ·
I created a spreadsheet with check boxes in column A (linked to the cell each check box sits on top of) and have amounts in column B. I use the SUMIF function to total the values up for each check box that is checked. Everything works fine, but I can't figure a way to easily link the check box with the cell it sits on. I have about 80 check boxes in my spreadsheet and have to individually link the box to the cell on each one. Is there an easier way?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

No guarentees but...

by Tig2 In reply to Excel 97 Check Boxes

You should be able to drag the first cell across the range of cells you wish to have the check box associated with. Position the cursor on the bottom right of the cell to copy until it appears as a black "plus" sign. Click and hold, drag the box through the range (e.g. A1 - A 20).

Collapse -

Thanks, but...

by jmbarnes In reply to No guarentees but...

tried that and it didn't work. It copies the checkbox to the cells I dragged across but associates all the newly created checkboxes back to the original cell. Thus, when I check one of the boxes, they all fill in.

Collapse -

That should be an easy fix

by Tig2 In reply to Thanks, but...

Once the range is selected, go to Edit-> Series and check that the value for the fill is stepped by one. That should associate the value (checked or not checked) only to the cell- I will bet that your series is set to duplicate in every cell. After you have filled the series, you should see it in the formula.

Collapse -

Tried that too

by jmbarnes In reply to That should be an easy fi ...

Maybe I didn't follow your instructions correctly. Here's what I did.
1. Inserted a box in A1.
2. Linked box to cell A1.
3. Dragged fill handle to right from A1:A5.
4. With fill range still selected, clicked Edit>Fill>Series and entered Step Value = 1, and selected Type=Linear.
5. Clicked <OK>.
All check boxes in cells A1:A5 are still linked to cell A1. What did I do wrong? FYI, I also tried this with Type=Growth and Autofill.

Collapse -

You did everything right

by Tig2 In reply to Tried that too

I have a sneaky that your Step 2, linking the box in A1 is what is holding this operation up. A macro may be required to override the link to A1 while still allowing the check box to link to the adjacent cells.

I will need to haul out my 97 box to give you a better answer.

I'm sure that you have tried copy/paste so that isn't the likely solution either.

I will play with this and let you know what I come up with.

Collapse -

Thanks, TiggerTwo

by jmbarnes In reply to You did everything right

I'm really stumped on this one.

Collapse -

There's both of us

by Tig2 In reply to Thanks, TiggerTwo

I have to believe that this can be accomplished but I have yet to pull it off. Near as I can tell, the action of linking the check box is the key but I have not yet found the magic formula for adjusting the link to associate to the cell the check box is in.

But I am nothing if not tenacious. The MS Knowledge Base has nothing I have yet found but I can't imagine that the task is impossible. Keep your fingers crossed- now that we know WHY the problem exists, I feel reasonably certain that we can solve it!

Related Discussions

Related Forums