Software

Ask something
Question Locked

Excel 97 Check Boxes

0 helpful
8 replies

by jmbarnes

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
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?

8 replies

by tig2

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).

by jmbarnes

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.

by tig2

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.

by jmbarnes

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 .
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.

by tig2

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.

by tig2

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!

This discussion is closed to new replies.