Solved

Bizarre Conditional Formatting results

Posted on 2014-11-10
9
83 Views
Last Modified: 2014-11-16
All,

I have an Excel sheet with three conditional formatting rules to enable easier reading of the data portrayed in the sheet but am getting unexpected results.

Rule 1:
=$D3<>$D4  
compares a task reference in column D and adds a border to the bottom cell when the task reference changes

Rule 2:
=$A3=1  
in column A I have the following formula:

=IF(B3="","",IF(D3=D2,A2,ABS(IF(A2="",A1,A2)-1)))

This alternates the value in column A between 1 and 0 based on the change of task reference in column D. This then formats a shade of green for the lines equal to 1.  The appearrance of the report is now green and white shading alternating with change in task reference.

Rule 3:
=AND($D3<>"",ISERROR(IFERROR(MATCH($D3,LoL!$E:$E,0),MATCH($D3,Enabling!$E:$E,0))))

Checks that the Task reference is included in the summary reports on other sheets, if not it highlights red.

Rules 1 & 3 seem to work fine. However rule 2 works but some rows end up with a different shade of green.

See attached screen shot.

Any suggestions??

Thanks
Rob H
CF-Screen-shot.PNG
0
Comment
Question by:Rob Henson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40433110
Please post your dummy example file
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40433115
What happens if you change the green color (say to blue). Do you have this difference in colors then?
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40433147
1 and 3 works becuase the result of the logical test, either is true or false or 1 or 0   which triggers the conditional formatting.

however, if you look are the 2 =IF(B3="","",IF(D3=D2,A2,ABS(IF(A2="",A1,A2)-1)))  

ABS(IF(A2="",A1,A2)-1))  this part would be fine, although i do not know the values of A1 and A2

IF(D3=D2,A2   would work fine if the A2 either hold 1 or 0   (you did not specify what value is in A2)

this one IF(B3="",""  this runs first if B3 is empty then will always return false if empty . because the return value is a null string empty and your rule 2 of =$A3=1  would return false.
0
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 33

Author Comment

by:Rob Henson
ID: 40433161
That's the bizarre thing, when I copy and paste values and formats into a copy file with a view to taking out the sensitive data, the CF works fine. I end up with alternating red and green because the red rule takes over because it can't find the task reference elsewhere but the green rule works ok, alternate lines are the same shade of green.

If you look at the screen shot I uploaded, the paler shade of green is the correct shade and is what I get for all alternate blocks on the copy file.

Thanks
Rob H
0
 
LVL 33

Author Comment

by:Rob Henson
ID: 40433199
Prof JJ - Rule 2 also gives TRUE or FALSE. The CF Formula is =$A3=1

The formula that you broke out is in cells in column A and that works fine, it alternates values 1 and 0 for alternating blocks based on the task reference in column D but allows for previous row being blank, shown on screen shot as left most column.

Philip - I tried with different colours and the same happened.

I tried removing Rule 2 and replacing but same result.

Thanks
Rob
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 40433306
Are the green ones getting red if you remove the task reference?
Seeing the screenshot the paler green seems to be wrong - the CF shows a darker one.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40434548
I think without the sample file (which only needs to contain columns A and D), we are a bit in the dark.
0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 0 total points
ID: 40435087
Hi All,

Thanks for your inputs but unfortunately I was not able to upload a sample file. As soon as I replicated in a sample file it worked fine.

So I decided to remove all CF, remove all manual cell fill formats and re-apply the CF. It now works fine.

Thanks
Rob H
0
 
LVL 33

Author Closing Comment

by:Rob Henson
ID: 40445484
Solved by removing and replacing CF rules.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question