check boxes and lists - are they possible?

[Philip Sterling]

I've got a robust list to which I would like to add 2 check boxes to
reduce redundant entries. The formula is extremely simple, and the
idea works well on an individual basis. However, I'd like to
implement the concept in my list. The problem is this: I can't seem
to add the actual check box to the list, where it would automatically
recur when a new record is added. I think this problem stems from the
fact that I can position a text box over a cell, and link it to any
cell, but I can't actually insert it into a cell (therefore, when the
cell is copied, the check box is not). Here's a basic example of the
type of problem I'm up against:


total M&Ms checkbox (green) checkbox (peanut)
green M&Ms peanut M&Ms
10 yes no
10 0


Basically, this would eliminate having to actually enter 10 green,
whereas I could just check the box. Any suggestions?

 

[Jim Gordon]

Hi Philip,

I'm not sure I completely understand the setup, but it seems to me there are
two things going on that Excel can do for you.

One thing is conditional formatting. If you want to change the formatting of
a cell based upon its contents check out Conditional Formatting on the
Format menu.

The other thing that Excel can do for you is to create a list for your users
to choose from. You'll need a cell range that has the values for the list
drop-down. Once you have that use the Data menu to turn on Data Validation.
Use the "list" method of validation.

-Jim Gordon
Mac MVP

All responses should be made to this newsgroup within the same thread.
Thanks.

About Microsoft MVPs:
http://www.mvps.org/

Search for help with the free Google search Excel add-in:
<http://www.rondebruin.nl/Google.htm>

 

[Philip Sterling]

Thanks for the reply, Jim. However, it's probably best if I restate
my problem, since I'm not trying to format or sort data, I'm trying to
input it:

1. User inputs integer into A1.
2. User selects either button_1 and button_2.
3. If button_1 is returned, A1's value is copied into C1, D1, and E1.
4. Else if button_2 is returned, A1's value is copied into F1 and G1.
5. This needs to be dynamic, so the user can select a different
button for each entry (i.e. A1, B1, C1, etc.)

This very simple idea works, with this code:
C1 example -----> =IF(Z1=1,C1=A1,"")
F1 example -----> =IF(Z1=2,F1=A1,"")

In the above example, Z1 is where the button selection result is
stored (hidden).

The problem that has me stumped is this:
How do I insert form objects (i.e. button groups) into specific cells,
or is this even possible? I'd like to have the cell's contents be a
button group, rather than just having the form object superimposed
over the entire page. I can align the object with a cell, but can't
actually insert it. Since I have a quite extensive list going (1,000+
lines), I'd like to incorporate this object so it will automatically
appear when I add a new entry to the list. I'd hate to have to copy
and paste the object for each new list entry! If all else fails, I
guess I can always use 1s and 0s in additional columns instead of the
buttons. It won't be near as pretty or quick, but it might be a lot
easier to set up.

Also, is there any way to resize form objects (check boxes and
buttons)? The resize/scale commands don't work on these objects.
They only resize the attached text. I'd like to make the buttons
smaller so they fit inside the cell boundaries.


Thanks for any help you might be able to provide.

-Philip