Conditional formatting problem

Solved/Closed
King_Of_Hearts13 - Apr 15, 2010 at 08:42 AM
King_Of_Hearts13 - Apr 19, 2010 at 05:52 AM
Ok I hope someone can help, I have a spreadsheet set up so that it flags up to me if two certain codes match by turning the cell yellow. I also have it set up so that the spreadsheet greys out a line when it has a completed date entered, but the second matching code stays yellow.

This is currently what is happening:

Fig 1.
Code__________Finish date
A1234 <-Yellow
A1234 <-Yellow

Fig 2.
Code__________Finish date
A1234 <-Grey......15/04/2010 <-Grey
A1234 <-Yellow

How can I stop the second code turning yellow when the matching code has a finished date?

4 replies

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Apr 19, 2010 at 05:43 AM
I had meant to type this too, but slipped my mind
You were wondering what I had meant by a working column. Basic idea is that have a column in a sheet that is based on formula.

You want to have a count of all distinct values in D, provided the cell J was blank. As I said, one way was to do the sumproduct. Other way would have been to use the last available column on the sheet and use it to your advantange. In your sample book, it seems column N is available
So i would write in N2 formula

=IF(J2<> "","", D2)
and I would copy it to the last row of data. This would basically put the value of column D in column N, if J was blank. So essentially now we have all the Ds for which there is nothing in J and this is what you wanted as now we can do the count of d, by excluding non-blank J rows.

So in conditional formatting, instead of your current =COUNTIF(D:D,D2)>1, I would have used =COUNTIF(N:N,N2)>1
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Apr 19, 2010 at 05:46 AM
Put your file with this new conditional formatting on shared site( with two rows of data)
King_Of_Hearts13
Apr 19, 2010 at 05:52 AM
I tried the countif version you mentioned above and it works! I think I might just go with this! Thanks for all your help and for being so patient with me! haha!
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Apr 15, 2010 at 09:19 AM
what are the formula now ?
You should have two formula

some thing like this

=AND(A1="A1234", B1="")
and then select via format of conditional formatting yellow color

=AND(A1="A1234", B1<>"")
and then select via format of conditional formatting gray color

In the example above, the is there is no data in B1 then i am selecting yellow color and if there is data i would be going for gray
King_Of_Hearts13
Apr 15, 2010 at 09:29 AM
Well there are more formulae, I just wanted to simplify the problem but all the fomulae i'm using are:
=\$J12>1 (which greys out the line when the finished date is input)
=AND(TRIM(\$G12)<>"",\$G12<=TODAY()) (which turns the line red when an examination date is overdue)
=COUNTIF(D:D,D12)>1 (which turns the code cells yellow when there are two or more cells with matching equipment codes)
Hope that helps you answer my question. Thanks!
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Apr 15, 2010 at 09:34 AM
Could you please upload a sample file with currect formatting instructions that you have on some shared site like https://authentification.site and post back here the link.
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Apr 15, 2010 at 09:53 AM
Its just a blank file? Hard to see where the issue is. Could you put at least two rows so one can see where the issue is
sharpman Posts 1021 Registration date Saturday May 23, 2009 Status Contributor Last seen October 20, 2010 183
Apr 15, 2010 at 02:46 PM
ok, I think i have the solution to your problem, but i need some example data to clarify.

please copy and paste some data here so i can see your problem.
Also copy your formula that you are currently using so i can compare and amend as needed.
King_Of_Hearts13
Apr 16, 2010 at 02:14 AM
Ok so say there are two reference codes that are the same in the Reference column, just put anything in as the code for argument just say A1234 is in D2 then put that same number below it in D3, they should both turn yellow (which is what i want), but then in the Confirmation Recieved column type in any date, lets say 15/04/2010 in J2, and that line will grey out and the reference number, D2, will no longer be yellow (which is what I want), but the other line with the matching reference number, D3, stays yellow which is the problem I'm trying to fix. Basically it's only supposed to go yellow when there are two or more examinations with the same reference number that are incomplete. Hope that helps? Let me know if you need anymore info. Cheers.
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Apr 16, 2010 at 04:36 AM
Hey it is possible to have a temp working column ?
King_Of_Hearts13
Apr 16, 2010 at 04:43 AM
Huh? What do you mean? Just add values in like I say above and you'll see what I mean.
King_Of_Hearts13
Apr 16, 2010 at 04:47 AM
P.S. the Conditional formula for cell D12 are:

=\$J12>1 (which greys out the line when the finished date is input)
=AND(TRIM(\$G12)<>"",\$G12<=TODAY()) (which turns the line red when an examination date is overdue)
=COUNTIF(D:D,D12)>1 (which turns the code cells yellow when there are two or more cells with matching equipment codes)
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Apr 16, 2010 at 04:50 AM
The problem is your countif statement, it is not taking into consideration that look at J column too, So I need a temp column to consolidate the info
sharpman Posts 1021 Registration date Saturday May 23, 2009 Status Contributor Last seen October 20, 2010 183
Apr 16, 2010 at 08:42 AM
rizvisa1 is close to what you want,

I realy need to see the data so i can point you in the right direction.
King_Of_Hearts13
Apr 16, 2010 at 08:54 AM
Ok here is the link, I tried it again with example data, hopefully you can access this one. Note though that the formulae are hidden as its conditional formatting I used and I'm not sure how to show that on this share site.

https://authentification.site/files/21969476/Pressure_System_Postponement_Tracker_Example_data.XLS
sharpman Posts 1021 Registration date Saturday May 23, 2009 Status Contributor Last seen October 20, 2010 183
Apr 16, 2010 at 09:14 AM
That link does not work. I have tried it and it complains that it cannot find the file
King_Of_Hearts13
Apr 16, 2010 at 09:16 AM
Weird!? I can click into it ok, dunno what's up? Any other way u know of so that I can upload it for u?
sharpman Posts 1021 Registration date Saturday May 23, 2009 Status Contributor Last seen October 20, 2010 183
Apr 16, 2010 at 09:36 AM
apparently, the only way i can get that file is if i go through a proxy and then i would have to either have an account or buy one ...........
sharpman Posts 1021 Registration date Saturday May 23, 2009 Status Contributor Last seen October 20, 2010 183
Apr 16, 2010 at 09:39 AM
ok ........ spooky moment ....... all of a sudden i can access speedyshare. i've downloaded the file now.
I'll have a look and get back to you.