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
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
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
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
