+ Reply to Thread
Results 1 to 11 of 11

I need formula to Choose only one of four cells in a multiple choice question

  1. #1
    Registered User
    Join Date
    05-05-2014
    Location
    Pennsylvania, USA
    MS-Off Ver
    Excel 2003
    Posts
    5

    I need formula to Choose only one of four cells in a multiple choice question

    So the 4 cells in a row (B5:E5) that I have each contain a different value. I want the user to only be able to choose one of the 4 cells - that is, if any of the 4 cells are filled in, I would like the other 3 to default to N/A (or somehow not be allowed to be chosen). Can someone provide me with the formula for this? Also, Can you tell me if there is a way that once a cell is checked the others gray out?

    Example of Question: Rate your Service Poor Fair Good Excellent
    X

  2. #2
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: I need formula to Choose only one of four cells in a multiple choice question

    Instead of multiple cells have you considered one cell with a dropdown box via data validation?

    Something like this:
    Book2.xlsx
    Last edited by gak67; 05-05-2014 at 10:51 PM.

  3. #3
    Registered User
    Join Date
    05-05-2014
    Location
    Pennsylvania, USA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: I need formula to Choose only one of four cells in a multiple choice question

    Yes but for this particular form I need this design. Any help would be appreciated. Thank you.

  4. #4
    Registered User
    Join Date
    08-12-2013
    Location
    USA
    MS-Off Ver
    Excel 2016, 32-bit
    Posts
    92

    Re: I need formula to Choose only one of four cells in a multiple choice question

    Quote Originally Posted by rescuetb View Post
    So the 4 cells in a row (B5:E5) that I have each contain a different value. I want the user to only be able to choose one of the 4 cells - that is, if any of the 4 cells are filled in, I would like the other 3 to default to N/A (or somehow not be allowed to be chosen). Can someone provide me with the formula for this? Also, Can you tell me if there is a way that once a cell is checked the others gray out?

    Example of Question: Rate your Service Poor Fair Good Excellent
    X
    Ok, so if I understand you correctly, the values in cells B5:E5 are the descriptions of the ratings (poor, fair, good, excellent). Then you want the user to put an X in the cell below that corresponds to the choice? So the X would go somewhere in the range B6:E6?

    And once a user puts a value in one of the cells in the range B6:E6, you want the others to gray out?

  5. #5
    Registered User
    Join Date
    05-05-2014
    Location
    Pennsylvania, USA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: I need formula to Choose only one of four cells in a multiple choice question

    Yes that is exactly what I am seeking!!


    Quote Originally Posted by njs27 View Post
    Ok, so if I understand you correctly, the values in cells B5:E5 are the descriptions of the ratings (poor, fair, good, excellent). Then you want the user to put an X in the cell below that corresponds to the choice? So the X would go somewhere in the range B6:E6?

    And once a user puts a value in one of the cells in the range B6:E6, you want the others to gray out?

  6. #6
    Registered User
    Join Date
    08-12-2013
    Location
    USA
    MS-Off Ver
    Excel 2016, 32-bit
    Posts
    92

    Re: I need formula to Choose only one of four cells in a multiple choice question

    I'm not sure you can actually have a formula in a cell where you want the user to type something. I can't think of anything at the moment. Maybe someone else can.

    But, I think it might be possible with VBA? You might want to post this (or ask an admin to move it) to the VBA sub-forum.

  7. #7
    Registered User
    Join Date
    08-12-2013
    Location
    USA
    MS-Off Ver
    Excel 2016, 32-bit
    Posts
    92

    Re: I need formula to Choose only one of four cells in a multiple choice question

    Or, have you thought about using Option Buttons in cells B6:E6? Those are mutually exclusive (i.e., if you click on, the others will be blank).

    this might help: http://www.contextures.com/xlForm01.html
    Last edited by njs27; 05-06-2014 at 10:12 AM.

  8. #8
    Registered User
    Join Date
    05-05-2014
    Location
    Pennsylvania, USA
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: I need formula to Choose only one of four cells in a multiple choice question

    Where do you access options?




    Quote Originally Posted by njs27 View Post
    Or, have you thought about using Option Buttons in cells B6:E6? Those are mutually exclusive (i.e., if you click on, the others will be blank).

    this might help: http://www.contextures.com/xlForm01.html

  9. #9
    Registered User
    Join Date
    08-12-2013
    Location
    USA
    MS-Off Ver
    Excel 2016, 32-bit
    Posts
    92

    Re: I need formula to Choose only one of four cells in a multiple choice question

    Quote Originally Posted by rescuetb View Post
    Where do you access options?
    I see you're using Excel 2003, and I don't have a copy of that anymore. They would be called "radio buttons" in that version though. Maybe look in the help file for Excel and search for that. I don't know though.

  10. #10
    Registered User
    Join Date
    08-12-2013
    Location
    USA
    MS-Off Ver
    Excel 2016, 32-bit
    Posts
    92

    Re: I need formula to Choose only one of four cells in a multiple choice question


  11. #11
    Registered User
    Join Date
    05-13-2013
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: I need formula to Choose only one of four cells in a multiple choice question

    I am a beginner with VBA Code, someone may have a better solution

    I have created examples one sheet with option buttons and one using VBA Code

    The "Using Radio Buttons" sheet I also added in an overall rating.

    On the "VBA Code" Sheet be sure to use a Capital X when selecting your answer and
    to change your answer you need to delete the old answer before selecting the new one.


    Book1.xls

    I hope this helps
    Dave

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Multiple Time Stamps depending on choice in Drop Down List Choice
    By insayah in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-25-2022, 09:53 AM
  2. [SOLVED] Formula help to choose lowest cost from multiple vendors and then choose vendor
    By roland_arv in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-27-2013, 07:48 PM
  3. Formula: Drop Down List -> Choose Option Finds Cells & Replace Cells with Cells
    By g00glethis1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-14-2010, 01:10 PM
  4. Replies: 4
    Last Post: 05-08-2007, 11:30 PM
  5. Replies: 8
    Last Post: 07-30-2006, 07:35 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1