# Excel Yes/No value creation



## gurutech (Apr 23, 2004)

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?


----------



## OBP (Mar 8, 2005)

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


----------



## gurutech (Apr 23, 2004)

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.


----------



## OBP (Mar 8, 2005)

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.


----------



## gurutech (Apr 23, 2004)

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?


----------



## OBP (Mar 8, 2005)

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"?


----------



## gurutech (Apr 23, 2004)

Could I do a "If Value = "yes" then CommandButton1.Caption = "yes" else CommandButton1.Caption = "no" ?


----------



## OBP (Mar 8, 2005)

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. :up: :down:


----------

