Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Excel Yes/No value creation


(!)

gurutech's Avatar
Computer Specs
Member with 2,925 posts.
THREAD STARTER
 
Join Date: Apr 2004
Location: Central NJ
Experience: Seasoned Professional
15-Feb-2007, 09:26 AM #1
Excel Yes/No value creation
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,651 posts.
 
Join Date: Mar 2005
Location: UK
15-Feb-2007, 10:11 AM #2
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
__________________
OBP
I do not give up easily
gurutech's Avatar
Computer Specs
Member with 2,925 posts.
THREAD STARTER
 
Join Date: Apr 2004
Location: Central NJ
Experience: Seasoned Professional
15-Feb-2007, 10:43 AM #3
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,651 posts.
 
Join Date: Mar 2005
Location: UK
15-Feb-2007, 11:00 AM #4
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's Avatar
Computer Specs
Member with 2,925 posts.
THREAD STARTER
 
Join Date: Apr 2004
Location: Central NJ
Experience: Seasoned Professional
15-Feb-2007, 12:27 PM #5
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,651 posts.
 
Join Date: Mar 2005
Location: UK
15-Feb-2007, 01:08 PM #6
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's Avatar
Computer Specs
Member with 2,925 posts.
THREAD STARTER
 
Join Date: Apr 2004
Location: Central NJ
Experience: Seasoned Professional
15-Feb-2007, 01:57 PM #7
Could I do a "If Value = "yes" then CommandButton1.Caption = "yes" else CommandButton1.Caption = "no" ?
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,651 posts.
 
Join Date: Mar 2005
Location: UK
15-Feb-2007, 02:31 PM #8
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.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑