Hands-on: Acer Predator Triton 700 review
Using Excel 2003 I know how to do conditional formatting on one cell, but is it possible to format several cells using the value of just one. I.e. is it possible to make a range of cells in a row switch to red font when one cell in the row has the value "PAY IN"?
or use the 'if' formula
for Excel 2000
Assuming the cell with "Pay in" is A1
select the cells you want to be formatted..
select "Formula is" and enter
Choose your format.
Thanks to all. That does one row. Now how do I "fill down" this conditional formatting so that the next row refers to A2, the third one to A3 etc? When I try to copy the formatting down several rows the cell ref remains as A1; if I try to enter the cell ref without the $ signs it doesn't work at all. If I fill in two rows manually, changing the second cell ref to $A$2 then select several rows and try "fill>series>step"1" nothing happens!
I'm sure I must be able to do this, so what am I missing?
Thank you very much VoG™; works a treat now just dragging the formatting down. Why is it that you know so much and the rest of us (me in particular) don't? I suppose at least I know where to ask!
the $ symbol in the formula keps the reference "absolute", and is/can be used for both the row and column reference...
Your original question asked "but is it possible to format several cells using the value of JUST ONE"
That's why I put the $ in front of both the A, (the column), and the 1, (the row).
Hope this helps. The $ symbol is often useful in this way.
I see now that the row must not be absolute or the filling down can't work. But why does conditional formatting of a range of cells not work without at least one absolute reference? I tried without the $ (i.e. just A1) in order to get the fill down, but the conditional formatting wouldn't take.
If you just wanted to fill down one column then using the relative address A1 should work.
However, as you want to format a whole row, then the $A1 is necessary such that each cell in that row is 'looking' at A1. If you set a conditional format in B1 that referenced A1 and tried to copy the formatting across the row then C1 would be 'looking' at B1, D1 at C1 etc. and the formatting would not work as expected.
a little confused; you're talking about a "row", but also refer to filling "down"? When you say "row" do you mean "column"?
Anyway... lets say you have something in A1, and you format cell B2 based on the contents of A1, say using the formula =A1>0 ...
As the formula uses "A1", (no $ signs), and you copy the format down column B, then the conditional format formula for cell B2 will be;
i.e. the formating for cell B2 is based on the condition of cell A2. This is the same as the formatting in cell B1, in as much as it's formatting is based on the condition of the cell immediately to it's left, (the same as for cell B2)... This is "Relative". There are no $ signs.
If the formula used in the formating for cell B1 was =A$1>0, and you then copy the format down column B, the formula for formatting in B2 would also be =A$1>0 By using the $ sign in front of the 1 you tell the formatting "stay with this row". If you use the $ sign in front of the A, you are telling the formatting, "stay with this column". If you use the $ sign in front of both you are telling it to stay with botht the row and the column... in other words, "stay with this cell" This is "absolute".
Does this help?
This thread is now locked and can not be replied to.