Conditional Formatting 1

[LGMan]

Hi, I've got to perform some traffic light reporting where better than target shows as green, and red denotes worse than target.
My data consists of approx 400 rows, where column C contains the Target figures and D to I contain the 6 month Trend.

So far in Cells D2:I2 I'm using conditional formats based around C2.

Is there a quick way to apply conditional formats to all the data in columns D to I so that the Conditional Format for whatever row will refer to what ever value is in col C for that row, eg D350 would refer to C350.

I've been looking at the MS help to no avail.

thanks in advance.

 

[SkipVought]

Hi,

COPY the row 2 cells

SELECT the cells you want the format in

Edit > Paste Special - Paste FORMATS

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LGMan]

Skip, I had alredy tried that, however it fixes the target cell as C2. If in I'm on cell D350, I need it to be compared to C350.

 

[SkipVought]

Post the EXACT expression in your CF.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LGMan]

Thanks Skip, It's those damn Dollar signs that get added, so it was like =$C$2.
just using =C2 works with the copy paste special.

 

[lionelhill]

The new conditional formats are a bit messy for this.

(1) You cannot use relative referencing in conjunction with icon sets (etc.). Obviously your format looks at the value in the cell that is being formatted, but you can make it compare to a value in another cell by typing a formula in the compare-with box when editing a conditional format. If you select the other cell, Excel will fill it in as an absolute reference ($C$2), and when you copy and paste, it will remain as $C$2, which is not what you want. If you attempt to enter $C2 so the row should increment, Excel will pop up a very specific error message saying you cannot use relative referencing.

(2) You can try to get round this using
=index(mydata, row())
where mydata is a named range including all the numbers with which you want to compare to see if you're doing better or worse.
You can type this in the box with which the data will be compared to decide on colour of icon. Unfortunately the result seems difficult to predict. Sometimes the icons all disappear, sometimes they reappear in different colours, it all looks very illogical.

(3) Or you can revert to good-old-fashioned conditional formatting using the background colour of the cell. Advantages: it's still do-able in green/amber/red, it's entirely Excel2003-compatible, and it works.

 

[SkipVought]

A

http://wwww,

come on! There is absolutely nothing damnable about absolute references.

It's like the force of friction. Comes in handy from time to time.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lionelhill]

AHA! Here's another way to achieve the same aim. You're going to really like this:

Set up extra columns between your columns that contain the 6 month trend data. In these columns, place a formula that returns a result 0-1-2 according to what colour you are going to want the traffic lights. Now put the traffic-light conditional formatting on these columns only (looking at number obviously), and specify that you only want the icon (not the value) displayed. Now format the whole thing so it looks as much as possible as though the icon and the adjacent column whose trend it displays are in fact a single column...

Pity about the alternating nature of columns this way; makes life hard to copy sideways, but can't have everything.

Good luck

 

[lionelhill]

LGMan or Skip, sorry for being thick, can you let me know how you did this? I've been trying to do this for weeks, and my copy of Excel2007 absolutely will not tolerate me putting any reference without $ signs in a formula associated with icon sets. I am totally stuck. I can't get to the stage of having a formula without $ signs to copy-paste special!

Sorry! I'd be really grateful.

 

[SkipVought]

lionelhill,

Are you answering LGMan's question or posting YOUR question?

If the latter, then please start a new thread.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lionelhill]

I am trying to do, I believed, exactly the same thing as LGMan. LGMan referred to "traffic light reporting" using conditional formatting, which I understood to mean using the traffic light icons in Excel2007 conditional reporting.

I have also tried to use these icons to indicate whether values are high or low relative to another cell in the same row, which is I believed is what LGman is doing.

You posted a helpful reply to LGman, which he clearly understood, and which worked for him. I cannot get it to work for me, which is why I asked for clarification.

It's almost certainly just that I am too stupid to follow the instructions and I've missed something daft, but on the chance that someone in future is equally stupid as me, and finds this thread while searching the archive for traffic light conditional formatting, I wondered if you or LGman could clarify?

I can happily post it as a new thread, if that would help, but it'll basically be exactly the same question.

Sorry to create misunderstanding.

 

[LGMan]

I'll have to admit that I'm using 2003, but am concerned whether my spreadsheet not look as intended with 2007.

 

[SkipVought]

but it'll basically be exactly the same question.

No, yours is directly related to CF in 2007 for ICONS. His is not.

BTW, your 'answer' has stuff about...

Set up extra columns between your columns that contain the 6 month trend data. In these columns, place a formula that returns a result 0-1-2 according to what colour you are going to want the traffic lights.

Whre did all THAT come from? LGMan is concerned about COLUMN D!

Please focus your responses on the OP's question.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lionelhill]

Thanks, LGMan, for letting me know you're using Excel2003.

Skip, sorry about my misunderstanding: give us a chance, though! I'm know I'm not too brainy, but the words "traffic lights" in the absence of any previous mention of Excel 2003 made me jump to the conclusion we were dealing with what Excel2007 specifically calls "traffic lights" in conditional formatting. All a hideous mistake on my part.

Incidentally, my later completely misguided post will make perfect sense to anyone who IS using Excel2007 traffic lights!

LGMan, no, your Excel2003 formatting will look fine in 2007. It's just vice versa that will come out missing bits.

 

[LGMan]

Thanks lionelhil for the clarification re Excel 2007. Apologies on my part as within my organisation traffic lights is the generic term for red, amber and green colours to ease the interogation of data. Have to remember the world is bigger than my organisation sometimes

 

[SkipVought]

Traffic Light emphasis is widely used to describe RED, YELLOW, GREEN CELL SHADING in workbooks.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lionelhill]

Thanks, I'll post my Excel2007 question clearly labelled as such and see if anyone can come up with something for my related problem. Apologies from me, LGMan, for my assumption you were using a feature from 2007. I tend, sadly, to assume others have on their desk exactly what I have on mine.