Excel Formula ? - Chicagoland Sportbikes
Chicagoland Sportbikes
 
Geek Squad to the rescue! fucken computer crashing on you again? another porn site infected your computer? tierd of your wife seeing your anal fisting videos and want to lock her out of the external HD? get the answers you need from our local computer experts.

 
LinkBack Thread Tools Display Modes
post #1 of 5 (permalink) Old 11-28-2010, 09:59 AM Thread Starter

 
PolishPete's Avatar
 
Join Date: Mar 2008
Location: Suburbs, IL
Posts: 2,471
Location: Suburbs, IL
Sportbike: 600rr
Years Riding: 7 years
How you found us: From friends
           
Excel Formula ?

I'm ok with Excel. I've got the basics down, and have gone through the help functions and web to figure out how to get my drop down lists, how I want them, plus some other neat stuff.

However, I've got one thing I'm trying to do, that I cannot seem to figure out.

I'd like a drop down list in cell A1 for example, that drops down 3 colors (red, yellow, green) and the user manually picks the color. I swear I've done this before, but cannot find that spreadsheet as reference, and cannot remember how I did it.

At best right now, I can get the colors to show up based on specific values inputted into that cell, or a different cell, however, I don't want the color to show up based on value, rather just have the colors show up on their own.

For example, I can have a drop down list with the numbers 1, 2, and 3. Based on which number you choose, the cell changes color, and the number shows up in the cell (along with the color). Sort of what I want, but really would rather there not be any numbers/values involved, and simply a drop down of 3 colors, and pick the color I want.

Anybody know how to do this?

(For what it's worth, I've been trying combinations involving data validation and conditional formatting/color scales, and can't seem to come up with it. Am I in the right direction?)
PolishPete is offline  
Sponsored Links
Advertisement
 
post #2 of 5 (permalink) Old 11-28-2010, 10:44 AM
Formerly RwS Films
 
PaulieRedCoat's Avatar
 
Join Date: Aug 2006
Location: A-whore-ra, IL (88 & Farnsworth)
Posts: 1,284
Location: A-whore-ra, IL (88 & Farnsworth)
Sportbike: 2007 Yamaha FZ6, Snow Fiddy
Years Riding: 2
How you found us: Other
           
Send a message via AIM to PaulieRedCoat
Put the list in some cells somewhere (for example Z1, Z2, Z3)
From the top menus... Data, Validation. Change the Allow setting to "list" then select your list (Z1-Z3)

Namaste

To the times I'll never remember, with the friends I'll never forget.
PaulieRedCoat is offline  
post #3 of 5 (permalink) Old 11-28-2010, 11:36 AM Thread Starter

 
PolishPete's Avatar
 
Join Date: Mar 2008
Location: Suburbs, IL
Posts: 2,471
Location: Suburbs, IL
Sportbike: 600rr
Years Riding: 7 years
How you found us: From friends
           
Quote:
Originally Posted by RwS Films View Post
Put the list in some cells somewhere (for example Z1, Z2, Z3)
From the top menus... Data, Validation. Change the Allow setting to "list" then select your list (Z1-Z3)
Thanks. I got that part to make a list. Easy.

Its the color part that's difficult for me.

If I just fill Z1-Z3 with red, yellow, green. I get a list in A1, but...the 3 options are just blank. I want the options to be color fill for that cell (A1).

If I input Z1-Z3 with the numbers 1, 2, 3...I can then make a formula for 1 to equal red, and so on, but then my drop down is 1/2/3, and when I click on #1, my cell becomes #1 filled with red. I just want the red...no #1. Hope that makes sense.
PolishPete is offline  
Sponsored Links
Advertisement
 
post #4 of 5 (permalink) Old 11-28-2010, 12:53 PM
Formerly RwS Films
 
PaulieRedCoat's Avatar
 
Join Date: Aug 2006
Location: A-whore-ra, IL (88 & Farnsworth)
Posts: 1,284
Location: A-whore-ra, IL (88 & Farnsworth)
Sportbike: 2007 Yamaha FZ6, Snow Fiddy
Years Riding: 2
How you found us: Other
           
Send a message via AIM to PaulieRedCoat
edit.

I didn't mean fill Z1 with Red. I meant type Red in the cell. same with Z2 and Z3

then go to...
Format, Conditional formating.

"Cell Value is" "equal to" Z1
edit format to be filled with that color.

Add, repeat.

Edit 2: see attached. I had to zip it because clsb doesn't allow uploading excel sheets but it does allow zip files.
Attached Files
File Type: zip Book1.zip (1.7 KB, 3 views)

Namaste

To the times I'll never remember, with the friends I'll never forget.

Last edited by PaulieRedCoat; 11-28-2010 at 01:05 PM.
PaulieRedCoat is offline  
post #5 of 5 (permalink) Old 11-30-2010, 08:24 AM Thread Starter

 
PolishPete's Avatar
 
Join Date: Mar 2008
Location: Suburbs, IL
Posts: 2,471
Location: Suburbs, IL
Sportbike: 600rr
Years Riding: 7 years
How you found us: From friends
           
Quote:
Originally Posted by RwS Films View Post
edit.

I didn't mean fill Z1 with Red. I meant type Red in the cell. same with Z2 and Z3

then go to...
Format, Conditional formating.

"Cell Value is" "equal to" Z1
edit format to be filled with that color.

Add, repeat.

Edit 2: see attached. I had to zip it because clsb doesn't allow uploading excel sheets but it does allow zip files.
Thanks for your help and the zip. Almost exactly what I was looking (except for no words involved)...but...I can definitely work with this and use it. Just going to change the words from the colors involved to Good/Ok/Bad and correspond to Green/Yellow/Red.

Thanks again!
PolishPete is offline  
Reply

Quick Reply
Message:
Options

Register Now



In order to be able to post messages on the Chicagoland Sportbikes forums, you must first register.
Please enter your desired user name, your email address and other required details in the form below.

User Name:
Password
Please enter a password for your user account. Note that passwords are case-sensitive.

Password:


Confirm Password:
Email Address
Please enter a valid email address for yourself.

Email Address:
OR

Log-in










Thread Tools
Show Printable Version Show Printable Version
Email this Page Email this Page
Display Modes
Linear Mode Linear Mode



Posting Rules  
You may post new threads
You may post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On
Trackbacks are On
Pingbacks are On
Refbacks are On

 
For the best viewing experience please update your browser to Google Chrome