What I am looking to do is for the cells in sheet 2, column "G" (hours) to highlight red if they are >.2 of values in sheet 1 column "E" (TGT) only if the sheet 2, column "D" (SEQ) and sheet 1, column "B" (operation) are identical.
enclosed is an example excel workbook. Any and all help would be greatly appreciated.
Last edited by cascencio83; 01-16-2015 at 02:07 AM. Reason: confusion
hi cascencio83. your questions and file is a little confusing. you first stated:
then your manual formula is:highlight red if they are >.2 of values
your file has all hours of 0.1. so i'm not sure which should be highlighted. also. in column D, they are numbers. "0001" in your formula is a text. so maybe you could give egs of which one you want to highlight. perhaps if SEQ 1 is 0.4 and Operation 1 is 0.1. is it highlighted cos it's 0.3 more than that? then give other egs like if SEQ 2 is 0.5 and Operation 2 is 0.3. it is 0.2 more, but not GREATER than 0.2. highlighted?=AND($D1="0001",$G1>0.3)
if my assumption is correct, try:
select the range you want to apply to (say from G2:G35)
go to Home -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is true:
=(G2-VLOOKUP(D2,Sheet1!$B:$E,4,0))>0.2
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
[QUOTE=benishiryo;3957937]if SEQ 1 is 0.4 and Operation 1 is 0.1. is it highlighted cos it's 0.3 more than that? then give other egs like if SEQ 2 is 0.5 and Operation 2 is 0.3. it is 0.2 more, but not GREATER than 0.2. highlighted?
Yes you are correct sorry. Your first question if operation# = Seq# and is >.2 highlight the cell is exactly what i want. As well as your 2nd question if operation#=seq# but <or= to .2 over do not highlight.
Your code helped point me in the right direction the correct code was =($G1-VLOOKUP($D1,Sheet1!$B1:$E1,4,0))>0.2
However when i was breaking down the formula to understand what you did what was the ",4,0" for? =($G1-VLOOKUP($D1,Sheet1!$B1:$E1,4,0))>0.2"
basically, it looks for D1 inside the 1st column of B:E i selected (i.e. column B:B). i then specify i want to return the 4th column from column B (i.e. column E:E) when i find D1 in column B. the 0 is the same as typing FALSE, and both is to find an exact match. so if D1 is "apple", "appleS" in column B will return you #N/A.
hope that helps
OK so on the sample book it works fine but on my actual file it does not. Could it be an issue that all the data on that sheet is being pulled from and access database that won't allow the formatting?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks