98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Conditional formatting
Conditional formatting
Resolved · Urgent Priority · Version 2003
Wed 18 Mar 2009, 15:30Delegate Rachael said...
Rachael has attended:
Excel Intermediate course
Excel Advanced course
Conditional formatting
how can you add a contional format to a work book if colums are different status
for example
Column K status is "converted" i would like the whole line to be green... (done that)
but if Column K is "converted" AND Column L is "invoiced" i would like the colour to change from green to red...
conditonal format formual using already
formual is
=INDIRECT("K"&ROW())="Converted"
thanks been working on this for months :)
Thu 19 Mar 2009, 09:20Trainer Stephen said...
RE: Conditional formatting
Hi Rachael
Thank you for your question
You need to create 2 rules, one for when column K = converted and when Column L does not equal invoiced, and a second were column K equals converted and column k equals invoiced.
Do do this select the first row of your data and set the following 2 formating rules as formulas
=AND($K15="Converted",$L15<>"Invoiced")
=AND($K15="Converted",$L15="Invoiced")
Note the cell reference has absolute columns and relative rows.
You can then copy the formatting down by holding down the right mouse button whilst hovering over the bottom right corner of the range, dragging down, letting go of the mouse and selecting copy formatting from the resulting context menu.
I have uploaded a workbook as an example
Hope this helps
Regards
Stephen
Attached files...
CondFormating.xlsFri 17 Apr 2009, 09:40Delegate Rachael said...
Formulas
Hi Stephen
wondered if you could help me i am currently using
=COUNTIF('Apr Activity'!$C$7:$AA$65536,"Admin")
formula to count and area of cells. however i now need to count those cell ONLY IF my first row "accountable" = ASR..
i think it would be easyier if i sent you a copy of what i am trying to do but can attach a file for some reason...
so imagine i have four small tables side by side this represents the weeks in the month.
each table has 5 colums in
Accountable /Date/Clien/action/Qualifying information & Next steps.
so i am telling my formula to read the WHOLE month and if its says admin count them up and give me a number. i now want the same but only if my first row of each table Accountable says ASR.. but i stil only want one figure i dont want 4 for each month..
Thanks
Wed 25 Mar 2009: Automatically marked as resolved.
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Adjusting the Elevation and Rotation in a 3-D ChartFor any 3-D chart you create, you can adjust the chart |
