Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    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.

  2. #2
    Plutonium Lounger
    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>

  3. #3
    4 Star Lounger
    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 contiguous---they 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 re-locate 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 ?

  4. #4
    Plutonium Lounger
    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?

  5. #5
    4 Star Lounger
    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.

  6. #6
    4 Star Lounger
    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]

  7. #7
    Plutonium Lounger
    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"?

  8. #8
    4 Star Lounger
    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--I 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 ??

  9. #9
    Plutonium Lounger
    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"?

  10. #10
    4 Star Lounger
    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.......

  11. #11
    Plutonium Lounger
    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?

  12. #12
    4 Star Lounger
    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......

  13. #13
    Silver Lounger
    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>

  14. #14
    4 Star Lounger
    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) double-checking and it may spruce up the appearance of the selection box....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •