Chit Chat
There is currently 1 person viewing this thread.
I'm struggling with this one and I can't seem to find the answer online.
Example
A1=100 / B1=A1-10 (90) / C1=B1-0.1 (89.9) / D1=C1-9.9 (80)
E1 is a manual daily input and is set up for CONDITIONAL FORMATTING as follows:
Greater Than A1 = RED (Background) / Between A1 & B1 = BLUE / Between C1 & D1 = Green / Less than D1 = Yellow
Every day I copy the last row and paste into the next row (1,2,3 etc) and then input the day's reading (E1,2,3) for an instant colour visual, and it works great... until I move the goal posts.
A week later and I'm now on Row 7 and I change A7 to 90. Cells B7:D7 all reduce automatically, however, the CONDITIONAL FORMATTING is still reading A1:D1 and not A7:D7 and this is no good if I want to retain earlier figures and formatting.
How do I keep what I have without having to continuously update the formatting on every goalpost move?
Issue Two
A1 has value and varying background colours depending on the input of another cell. C1 is just a statement (written) however, I want C1 to have the same background colour of A1 and I can't seem to do that either.
Example
A1=100 / B1=A1-10 (90) / C1=B1-0.1 (89.9) / D1=C1-9.9 (80)
E1 is a manual daily input and is set up for CONDITIONAL FORMATTING as follows:
Greater Than A1 = RED (Background) / Between A1 & B1 = BLUE / Between C1 & D1 = Green / Less than D1 = Yellow
Every day I copy the last row and paste into the next row (1,2,3 etc) and then input the day's reading (E1,2,3) for an instant colour visual, and it works great... until I move the goal posts.
A week later and I'm now on Row 7 and I change A7 to 90. Cells B7:D7 all reduce automatically, however, the CONDITIONAL FORMATTING is still reading A1:D1 and not A7:D7 and this is no good if I want to retain earlier figures and formatting.
How do I keep what I have without having to continuously update the formatting on every goalpost move?
Issue Two
A1 has value and varying background colours depending on the input of another cell. C1 is just a statement (written) however, I want C1 to have the same background colour of A1 and I can't seem to do that either.
sort by:
Show
per page
Replies: 24
|
By:
I will check back later, but any help in resolving these issues would be appreciated
|
|
By:
Have you tried putting an absolute reference on your conditional formatting ?
|
|
By:
Are you using jumpers for goal posts ?
|
|
By:
I'm not quite sure what you mean Angoose?
|
|
By:
It sounds to me that you shouldn't be using absolute references but are.
Does you first rule look like this =$E$1>$A$1 (this is absolute referencing) or this =E1>A1 |
|
By:
Or even e1>$A$1
When you cut&paste the above e changes but A does not. |
|
By:
Does conditional formatting using a value from another cell actually work?
|
|
By:
Conditional formatting is very flexible, but you require to be clear as to what it is you actually want.
I have a daily tracker of the brent crude oil price that uses conditional formatting to highlights the monthly highs and lows. You can copy the formatting from one monthly column to another and get what you want. Top tip is to check the formatting once you have copied, is it referring to the cells that you want it to. If not, change it and figure out why it didn't do what you wanted it to do. A bit of trial and error will help you understand what is going on. |
|
By:
ffs - < $A$1
|
|
By:
Tell a lie, it is > $A$1
|
|
By:
I love playing with this Angoose, all self-taught with tips gained from the internet and on here. I'm far from experienced but I keep playing with, trying to understand it, but if all else fails I just have to ask. This one is stumping me.
|
|
By:
are you putting eg greater than =A1 in the format box?
|
|
By:
No, that doesn't work. To get that to work I just ensure my cursor is clicked in the format box and then click on A1 and I get the > $A$1 come up and it sets correctly
|
|
By:
I think that's right... let's just check it, brb
|
|
By:
Yep, that's how it works
|
|
By:
ok if I pick a cell and go to the conditional formatting icon, click on the "greater than" option and put in "=A1" then that cell will refer to cell A2 if I copy it down a row.
|
|
By:
Aspro just get rid of the dollar signs (to make it relative addressing rather than absolute).
|
|
By:
Excellent Charlie, I think you may have hit the nail on the head! I'll be back if I have further problems, but this is looking promising. Thanks for the input guys!
As for your point dave, that is the theory I'm about to test now |
|
By:
Pressing the F4 key normally swaps between absolute and relative addressing but I can't test it because my function keys are full of gung thanks to spilling red wine, beer and other stuff on them!
|
|
By:
I'll test that out a little later Charlie, but I can now see what benefits both formats would have. An unexpected additional lesson learned.
|
|
By:
Well done Aspro. It's useful to learn the difference. You can also use mixed addressing such as $A1 (when the row will change but not the column) or A$1 when the column will change but not the row. In your case $A1 is what you'd need. Just play around with them to get comfortable with them.
|
|
By:
It's has taken a few hours to build but all appears to be perfect and updating exactly as I would have wished. So relieved, thanks again Charlie!
|