1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Yes/No value creation

Discussion in 'Business Applications' started by gurutech, Feb 15, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. gurutech

    gurutech Thread Starter

    Joined:
    Apr 23, 2004
    Messages:
    2,959
    I have an excel spreadsheet that I am using with several VLOOKUP values referencing customer survey questions. There are several ticket numbers one one sheet that each have a series of questions asked to the customer, along with a rating of 1-5. I have a list validation with a dropdown that points to a ticket #, and based on that ticket number, the appropriate questions are displayed for that question. I also have a tech assigned to each of the tickets, so when a ticket # is chosen from the dropdown, the appropriate tech's name is also displayed.

    What I am trying to do is have a checkbox, Yes/No, or True/False cell where the tech can toggle the value for each ticket. I know I can create a "third" column for the Yes/No value with another VLOOKUP to tie in to each ticket, but how can I get the value to be displayed based upon what the tech is choosing in the same cell?

    ie. I want cell A1 to be a vlookup for the value in B1 (ticket # chosen from dropdown) a range of C1:C20 (list of ticket #'s), but I also want the tech to be able to choose the value.

    I know this can be done in Access with a simple checkbox. Can the same be done in Excel?
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    gurutech, as you have metioned Access, why aren't you doing this in Access, as it is so much easier?

    You can use a Tick box and use it's "LinkedCell" property to put the value anywhere that you like to be picked up and used.
    For instance the Tick box can be on sheet1 and puts it's value (true/false) in cell "a1" on sheet 2.
    put his in the Tick Box's LinkedCell - sheet2!a1
     
  3. gurutech

    gurutech Thread Starter

    Joined:
    Apr 23, 2004
    Messages:
    2,959
    Access isn't giving me the display properties that I need, but Excel does.

    The problem with Excel is that I need the cell to be user-editable on a "per ticket" basis, not on a "per spreadsheet" basis.

    I think I can do it if I create a "button" in Excel, just not sure how this is done.
     
  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    gurutech, out of interest what "Display Properties" doesn't Access give you?
    Without seeing your Workbook I can't quite understand why you can't use Tick Boxes in Cells,
    if you want to create Command Buttons they are very easy, but I don't see the advantage.
     
  5. gurutech

    gurutech Thread Starter

    Joined:
    Apr 23, 2004
    Messages:
    2,959
    It's kinda hard to explain without sending the spreadsheet, but I can't do that because there is some confidential information contained in the spreadsheet.

    I think I came up with a solution, but I don't know how to implement it.

    I think what I need is a button that will change a value from Yes to No, or No to Yes in a particular cell, and then display the current value of the cell as the "label" of the button. So if cell A1's value is Yes, then the button will say Yes. If A1 is No, then the button will display No.

    Can that be done through a VBA script?
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Yes that is no problem to do.
    You can either the value of the cell like this
    Me.CommandButton1.Caption = Range("a1")
    or
    Me.CommandButton1.Caption = Cells(1, 1).Value

    Can you work out the "swithing method"?
     
  7. gurutech

    gurutech Thread Starter

    Joined:
    Apr 23, 2004
    Messages:
    2,959
    Could I do a "If Value = "yes" then CommandButton1.Caption = "yes" else CommandButton1.Caption = "no" ?
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    close
    if range("a1").value = "yes" then CommandButton1.Caption = "No"
    range("a1").value ="No"
    Else
    CommandButton1.Caption = "Yes"
    range("a1").value ="yes"
    end if

    I think that is correct, the reason being that you want the Cell value to switch from what was there to the opposite value WHEN you click the Button as well as the Button caption.

    Try it and see how it goes. (y) (n)
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/544266