Results 1 to 14 of 14

20051205, 04:10 #1
 Join Date
 Oct 2005
 Posts
 599
 Thanks
 1
 Thanked 1 Time in 1 Post
Choosing from 3 possibilities (Excel 2003)
I am making a chart that employees will fill out. They will be asked 3 questions: (1) do they work 50%, or, (2) do they work 60%, or, (3) do they work in the office. When they type an 'x' into one of the 'answer' boxes (for example, 2...indicating that they work 60%) the phrase "60% employee" will appear.
I need a formula that will look to see if the display shows "50% employee"or "60% employee" or "I work in the office"....(only 1 pharse will show).
The formula should then 'copy' that phrase into another page of the spreadsheet....anyone have any ideas of how to make a formula that can do this ?...thanks in advance.

20051205, 06:57 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Choosing from 3 possibilities (Excel 2003)
What are the "answer boxes"? If they are cells in the worksheets, you probably have a situation like this:
<table border=1><td></td><td align=center>A</td><td align=center>B</td><tr><td align=center>1</td><td>Do you work 50%</td><td align=right>

20051206, 01:40 #3
 Join Date
 Oct 2005
 Posts
 599
 Thanks
 1
 Thanked 1 Time in 1 Post
Re: Choosing from 3 possibilities (Excel 2003)
Hi Hans......thank you for that advice...I tried to use it and it wouldn't work.....all cells are on the same worksheet; the cells where the "x" goes are not contiguousthey are 3 sets of 'merged cells' (eg: ag7+8 which is now cell ag7, ag9+10 which is now cell ag9, y14+15 which is now cell y14).....can I use merged cells? must they be contiguous?
Also, the "50% emp", "60% emp" and "work in office" are contigous to each other [they are cells AM6, AM8, AM10] but not contiguous to where the "x" is placed.....can these be merged (am5+6, am7+8, am9+10) to produce AM6, AM8, & AM10 ?....and must they be contiguous to where the "x" is marked ?......thanks for your very useful help.....if it makes any difference, I can relocate the cells etc but I would really like to keep them merged (to provide bigger cells to mark the "x' into).....thanks...I await your reply....djm...ps: how did you produce the little chart in your answer ?....that might permit me to show you what I am working with ?

20051206, 07:18 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Choosing from 3 possibilities (Excel 2003)
I used the macro from <post:=164,109>post 164,109</post:> to create the table in the post. This macro won't handle merged cells, though.
Could you attach a small sample workbook to a post?

20051206, 07:52 #5
 Join Date
 Oct 2005
 Posts
 599
 Thanks
 1
 Thanked 1 Time in 1 Post
Re: Choosing from 3 possibilities (Excel 2003)
Hi Hans.....I am attaching a small sample.......if you delete the "x", you will see info disappear; it is conditionally formatted to appear and will be hidden in the final product. I want employees to indicate if they are 40%, 50%, 60% or office employees (using an "x') and then have the selection drop into the cell below (which actually is elsewhere, but I put it here for your purposes). Thanks for your help; I look forward to seeing what you come up with.

20051206, 07:55 #6
 Join Date
 Oct 2005
 Posts
 599
 Thanks
 1
 Thanked 1 Time in 1 Post
Re: Choosing from 3 possibilities (Excel 2003)
..ps Hans...and by now it should actually be called "Choosing from 4 possibilities"...40, 50, 60, office.....[img]/forums/images/smilies/smile.gif[/img]

20051206, 08:03 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Choosing from 3 possibilities (Excel 2003)
I would never use merged cells for this, you can vary the row heights to suit your purposes.
Is it OK to enter more than one "x"?

20051206, 13:48 #8
 Join Date
 Oct 2005
 Posts
 599
 Thanks
 1
 Thanked 1 Time in 1 Post
Re: Choosing from 3 possibilities (Excel 2003)
Hi Hans....thank you for thatI will get rid of the merged cells.....as far as using more than 1 x, I did that only to show you the "hidden" text that comes to light once an "x" is entered...I hadn't thought that poeple would need to use more then 1 "x" but if they do, is there a way to do that and have multiple choices (eg: 60% employee and working in the office) shown ??

20051206, 13:59 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Choosing from 3 possibilities (Excel 2003)
What would you like? Multiple lines in the result cell, one for each "x"?

20051206, 14:04 #10
 Join Date
 Oct 2005
 Posts
 599
 Thanks
 1
 Thanked 1 Time in 1 Post
Re: Choosing from 3 possibilities (Excel 2003)
Well, if it could all be contained within 1 cell, that ywould be great, but if I must, I suppose that I may have to create corresponding "result" cells (one for each "x") but not sure.......

20051206, 14:23 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Choosing from 3 possibilities (Excel 2003)
Something like this?

20051206, 14:30 #12
 Join Date
 Oct 2005
 Posts
 599
 Thanks
 1
 Thanked 1 Time in 1 Post
Re: Choosing from 3 possibilities (Excel 2003)
...yes...just like that...looks great.....thank you so mcuh for your help......

20051206, 14:31 #13
 Join Date
 Mar 2001
 Location
 Springfield, Ohio, USA
 Posts
 2,136
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Choosing from 3 possibilities (Excel 2003)
Seems like you should just use data validation. You don't want someone to check both 50 & 60%. See attached sheet for an example. HTH Sam
<font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
<small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

20051206, 14:54 #14
 Join Date
 Oct 2005
 Posts
 599
 Thanks
 1
 Thanked 1 Time in 1 Post
Re: Choosing from 3 possibilities (Excel 2003)
...thank you fo that suggestion also, Sam.......I will look at it b/cit eliminates (accidental) doublechecking and it may spruce up the appearance of the selection box....