Need an EXCEL Guru... - 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 11 (permalink) Old 03-08-2011, 06:41 PM Thread Starter
Slow Old Guy.
 
Wink's Avatar
 
Join Date: Sep 2005
Location: SS Barrington
Posts: 17,258
Location: SS Barrington
Sportbike: Inline Two Wheeled
Years Riding: Since the beginning of time
How you found us: Dan Ortega sent me
           
Need an EXCEL Guru...

I want to format a cell with Conditional Formatting and change the color of the NUMBER in the result cell based on the condition.

IN my example:

IF the CELL value is WW, then the Target Cell is Changed to GREEN
IF the CELL value is LL, then the Target Cell is Changed to RED
IF the CELL value is LW, then the Target Cell is Changed to BLACK
IF the CELL value is WL, then the Target Cell is Changed to BLACK

That is the hard part. Now for the simpler part. This is to manage Handicaps in a League format. League is multiple weeks. Lets assume 12 weeks long.

Week 1 value would be WW, LL, LW, or WL. (Win Win, Lose Lose, etc...)
That entry would change the handicap up or down, I already have that part figured out. However, I need the most recent entry each week to change the color automatically as described above.

Any suggestions? And yes, I do understand conditional formatting. Anyone wanna help me out with this one?

===========
Great Quote - One would think that the Secret Service was smart enough to get serviced secretly.

MotoVid hasbeen
NESBA hasbeen

CCS neverbeen
WERA neverbeen
Wink is offline  
Sponsored Links
Advertisement
 
post #2 of 11 (permalink) Old 03-08-2011, 06:44 PM
Registered User
 
project15's Avatar
 
Join Date: Apr 2009
Location: Wheeling, IL
Posts: 855
Location: Wheeling, IL
Sportbike: 1998 GSX-R600
Years Riding: 4th season
How you found us: Sportbikes.net
      
I can write you a macro, but do you want a macro or do you want it automatic? I'm guessing automatic.

-Chris
project15 is offline  
post #3 of 11 (permalink) Old 03-08-2011, 06:50 PM
Registered User
 
project15's Avatar
 
Join Date: Apr 2009
Location: Wheeling, IL
Posts: 855
Location: Wheeling, IL
Sportbike: 1998 GSX-R600
Years Riding: 4th season
How you found us: Sportbikes.net
      
Ok, I got it. give me a minute

-Chris
project15 is offline  
Sponsored Links
Advertisement
 
post #4 of 11 (permalink) Old 03-08-2011, 06:54 PM
Registered User
 
project15's Avatar
 
Join Date: Apr 2009
Location: Wheeling, IL
Posts: 855
Location: Wheeling, IL
Sportbike: 1998 GSX-R600
Years Riding: 4th season
How you found us: Sportbikes.net
      
In Word 2003:

Select the target cell with the number in it that you want formatted
Go to Format > Conditional Formatting
Use the formulas that I've included in the screenshot where B1 is your Cell value
Change your format for Condition 1 to Green font
Change your format for Condition 2 to Red font

Note: your cells should be black font by default for this to work. Conditional formatting only lets you add 3 conditions total so I can't add the LW and WL as black.
Attached Images
File Type: jpg Formula.JPG (70.5 KB, 11 views)

-Chris

Last edited by project15; 03-08-2011 at 06:56 PM.
project15 is offline  
post #5 of 11 (permalink) Old 03-08-2011, 06:55 PM Thread Starter
Slow Old Guy.
 
Wink's Avatar
 
Join Date: Sep 2005
Location: SS Barrington
Posts: 17,258
Location: SS Barrington
Sportbike: Inline Two Wheeled
Years Riding: Since the beginning of time
How you found us: Dan Ortega sent me
           
Chris, can I give you a call tomorrow? I have the part that you figured out, BTW< very fast, color me impressed.

I can send you the sheet that I am using and you will see how the second condition of the multiple weeks causes the problem.

Here this might help explain, the initial value never changes, the CURRENT handicap goes up and down based on Win/Lose record.


===========
Great Quote - One would think that the Secret Service was smart enough to get serviced secretly.

MotoVid hasbeen
NESBA hasbeen

CCS neverbeen
WERA neverbeen

Last edited by Wink; 03-08-2011 at 07:00 PM.
Wink is offline  
post #6 of 11 (permalink) Old 03-08-2011, 07:09 PM
Registered User
 
project15's Avatar
 
Join Date: Apr 2009
Location: Wheeling, IL
Posts: 855
Location: Wheeling, IL
Sportbike: 1998 GSX-R600
Years Riding: 4th season
How you found us: Sportbikes.net
      
Quote:
Originally Posted by Wink View Post
Chris, can I give you a call tomorrow? I have the part that you figured out, BTW< very fast, color me impressed.

I can send you the sheet that I am using and you will see how the second condition of the multiple weeks causes the problem.

Here this might help explain, the initial value never changes, the CURRENT handicap goes up and down based on Win/Lose record.
Oh that helps a lot, but I don't know a way to do it without running through a macro. I can probably take a look at it Thursday night if you send it to me. PM me if you still want me to write a macro for you.

Actually, you might be able to make an invisible cell that is equal to the last value in the row and then base the conditional formatting off that.

-Chris
project15 is offline  
post #7 of 11 (permalink) Old 03-08-2011, 07:32 PM
Registered User
 
project15's Avatar
 
Join Date: Apr 2009
Location: Wheeling, IL
Posts: 855
Location: Wheeling, IL
Sportbike: 1998 GSX-R600
Years Riding: 4th season
How you found us: Sportbikes.net
      
Ok, I figured it out, but don't have time to explain it or write it down Here is the formula:

In the last column, put this into a row that has your WW,WL,etc... values. This column will eventually be hidden. You'll base your number cell on this cell. I can fill this in for you if you email me the excel. I've PM'ed you my email address.

=LOOKUP(2,1/(A2:C2>""),A$2:C$2)

-Chris
project15 is offline  
post #8 of 11 (permalink) Old 03-08-2011, 10:13 PM
Registered User
 
project15's Avatar
 
Join Date: Apr 2009
Location: Wheeling, IL
Posts: 855
Location: Wheeling, IL
Sportbike: 1998 GSX-R600
Years Riding: 4th season
How you found us: Sportbikes.net
      
I've been able to spend a little more time looking at this and got it working exactly as you want it. I have a sample excel file I can send you or just send me the file and I'll put these changes straight into it.

-Chris
project15 is offline  
post #9 of 11 (permalink) Old 03-08-2011, 11:03 PM

 
gkotlin's Avatar
 
Join Date: Jul 2006
Location: NW Burbs
Posts: 4,565
Location: NW Burbs
Sportbike: SV 650, FZR 400, RM 125
Years Riding: Since 1990
How you found us: Nesba!
           
Nice! Your rock.

Greg K.
STT Staff Member
CCS Expert #12
MSF Instructor

VinylSaurusRex.com - Cyclepath Racing - Safety First Racing - SMR Components - Apexjunkie.com - Yamaha Champions Riding School
"If you're headed toward trees, I'm guessing your on the wrong line." - S. Russell
gkotlin is offline  
post #10 of 11 (permalink) Old 03-08-2011, 11:04 PM Thread Starter
Slow Old Guy.
 
Wink's Avatar
 
Join Date: Sep 2005
Location: SS Barrington
Posts: 17,258
Location: SS Barrington
Sportbike: Inline Two Wheeled
Years Riding: Since the beginning of time
How you found us: Dan Ortega sent me
           
Sheet sent!

===========
Great Quote - One would think that the Secret Service was smart enough to get serviced secretly.

MotoVid hasbeen
NESBA hasbeen

CCS neverbeen
WERA neverbeen
Wink is offline  
post #11 of 11 (permalink) Old 03-10-2011, 04:43 PM Thread Starter
Slow Old Guy.
 
Wink's Avatar
 
Join Date: Sep 2005
Location: SS Barrington
Posts: 17,258
Location: SS Barrington
Sportbike: Inline Two Wheeled
Years Riding: Since the beginning of time
How you found us: Dan Ortega sent me
           
MAJOR MAJOR Props to Chris!

PERFECT job did exactly what I needed, thanks so much again. I owe ya a beer or two!

===========
Great Quote - One would think that the Secret Service was smart enough to get serviced secretly.

MotoVid hasbeen
NESBA hasbeen

CCS neverbeen
WERA neverbeen
Wink 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