Excel 2007 : Conditional Formatting

[Lloyd04]

Hi guys,

I have a document with multiple worksheets, each is a series of results for that day and it will be updated every day.
From the second sheet onwards i wanted to include the conditional formatting Up and Down arrows, to express whether or not the data has risen or fallen. However without doing it cell by cell i don't know how to do it across the whole worksheet!
Everytime i copy the format to another cell it copies the identical formula not the next cells data.

I hope this makes sense, ask any questions that i haven't explained well enough please.

Also if ANYONE knows how to get the up arrow to be red without a seperate cells along side that would be amazing!

Thanks

[dip11]

The icon sets are fixed, you cannot change them. I would love a green down arrow myself.

For conditional formatting, it probably does not change the reference because you have used $A$1.
If you want it to go down the row, use $A1
If you want it to go across the column, use A$1

[Lloyd04]

Thanks for getting back to me.

I'll give up on the arrows then! Will use the grey scale ones.

I tried as you said.

I highlighted 1 cell on sheet 2 and then it automatically added the $ '='21.06.2012'!$B$5' so i deleted the second on around the cell number and got an erro message saying i cannot use relative references in conditional formatting?

Am i doing this right. I click the cell on the 2nd sheet - format - manage rule - change type to 'formula' then select the identical cell from sheet 1 as my value?

[dip11]

Hm, yes there are restrictions on relative references on some conditional formatting, such as icon sets.
If you use the "formula to determine whichc ells to format" that does not have any such restrictions.
I'm afraid the only other way to apply what you want is via either manual work or VBA (which basically you enter a select range and it does it for you)

Bob Philips posted a solution for me for a similar issue:
http://www.excelforum.com/showthread...=1#post2786028

[JCHASE FSP]

similar issue with conditional formatting
in column d ( going down the column) i have figures reading from a seperate file "file d1"

in column f ( going down the column) i have figures reading from a seperate file "file f1"


i would like to create a conditional format that takes the figure in column F
and compares is to column D

if the figure in column f is "less than" column d it should show in green.

when i copy this format down the column it doesnt adjust it copies the forumla(condition) verbatum ...

ie. cell F5 looks to cell D5 and comes back as "less than" : copy the condition to cell F6 i would like F6 to look at D6 not D5



any ideas?

[Lloyd04]

Ok i guess it's a pretty tricky idea then..

I've decided to use the Windings tactic but how can i make a formula that looks across the 2 worksheets and returns for example...

Sheet 2 b1 = n if equal to Sheet 1 b1
Sheet 2 b1 = m if greater than Sheet 1 b1
Sheet 2 b1 = e if less than Sheet 1 b1

if that makes any sense

[dip11]

=IF(B1=Sheet1!B1,n,IF(B1>Sheet1!B1,m,IF(B1<Sheet1!B1,e,"")))

[Lloyd04]

Perfect! You've been a great help!

Without having to manually select each of the up, down and side arrows can i colour them automatically?

[dip11]

This is something I found in a different post:

Please Login or Register  to view this content.

You need to highlight the cells you want to change the colors for and then run the macro.

Remember to mark your thread as solved when your issue has been taken care of and to award points to helpful members via the star button to the left of their posts.

[Lloyd04]

I have done as you said in terms of the points reward. You've been a great help.

About the macro though i'm unsure how this works (Never used them before)

[dip11]

Go to view macros, create new macro (enter a name first) and then replace the content of the window that opens with the above code.

Then close/save and select the windings you want to change and go to view macros again and click Run on that macro.