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 formating
Conditional formating
Resolved · High Priority · Version 2007
Fri 9 Jul 2010, 17:31Delegate Vicky said...
Vicky has attended:
No courses
Conditional formating
Hi Anthony,
Have you had a chance to think about the formating for that invoicing spreadsheet we were talking about?
I've been playing around with it and nothing seems to be working to get it to do two instructions in one formula.
Thanks,
Vicky
Mon 12 Jul 2010, 11:30Trainer Anthony said...
RE: Conditional formating
Hi Vicky, thanks for your query. I believe I have had some success. Open a new spreadsheet and select cell A1. Then choose Conditional Formatting, New Rule and choose "Use a Formula". Paste the following into the field at the bottom and choose a format.
=AND(A1="hello",B1="hello")
The idea is that both cells have to say hello before A1 is formatted. I have chosen this example because I seem to recall on your invoice sheet you are testing a value on the left of the worksheet and the value of the cell currently selected. You should be able to modify this to suit your own spreadsheet, and then tweak the cell references so that they are absolute references to the appropriate column (i.e. $A1, to always test what's in column A regardless of the row number of the active cell). Any problems, just let me know.
Anthony
Mon 12 Jul 2010, 14:08Delegate Vicky said...
RE: Conditional formating
Hi Anthony,
I have tried to put this formula into the sheet, and it does work, except, it formats the baseline a colour. I tested it with hello instead of numbers and it turns the cell with baseline in, to another colour, so it is formating that cell and not the hello cell.
Any thoughts as to why that might be happening? I swapped the order the formula was written in, puting hello first but that didnt make a difference.
Also, as this is for figures being put into the cell, how would I write the "B1=hello" if I want it to be for figures >0?
Thanks,
Vicky
Mon 12 Jul 2010, 14:57Trainer Anthony said...
RE: Conditional formating
Hi Vicky.
Try this as the formula for the conditional format:
=AND($A2="baseline", ISBLANK(H2)=FALSE)
I'm putting a baseline heading on Column A and writing "baseline" into various cells in that column. Then I'm going over to H2 (you will select a whole range of cells on the right hand side, but the principal is the same) and using the formula above. The end result is if I put a number into H2 and it says baseline in column A, the cell is formatted, but only if both conditions are true.
If this works, try using format painter to apply the conditional format across the cells you're interested in. It might work, but I'd prefer to lock things down with some absolute references in the formula itself.
Hope this helps,
Anthony
Mon 19 Jul 2010: 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:Create your own custom list on Excel 2010!If you know how to use the auto-fill option on Excel then why not create your own customs lists? |
