Conditional formatting problem
Solved/Closed
Related:
- Conditional formatting problem
- Clear formatting in excel - Guide
- Phone formatting software for pc - Download - File management
- Formatting usb mac - Guide
- Code for formatting android phone - Guide
- Openoffice conditional formatting - Guide
4 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Apr 19, 2010 at 05:43 AM
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
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
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Apr 15, 2010 at 09:19 AM
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
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
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!
=$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
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Apr 15, 2010 at 09:34 AM
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.
No problem here is the link:
https://authentification.site/files/21954386/Pressure_System_Postponement_Tracker_question_upload.XLS
https://authentification.site/files/21954386/Pressure_System_Postponement_Tracker_question_upload.XLS
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Apr 15, 2010 at 09:53 AM
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
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.
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.
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
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Apr 16, 2010 at 04:36 AM
Apr 16, 2010 at 04:36 AM
Hey it is possible to have a temp working column ?
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)
=$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
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Apr 16, 2010 at 04:50 AM
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
Apr 16, 2010 at 08:42 AM
rizvisa1 is close to what you want,
please could you link to an example of your data. the above posted link does not work - errors out with a
The requested URL /files/21954386/Pressure_System_Postponement_Tracker_question_upload.XLS was not found on this server.
I realy need to see the data so i can point you in the right direction.
please could you link to an example of your data. the above posted link does not work - errors out with a
The requested URL /files/21954386/Pressure_System_Postponement_Tracker_question_upload.XLS was not found on this server.
I realy need to see the data so i can point you in the right direction.
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
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
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
sharpman
Posts
1021
Registration date
Saturday May 23, 2009
Status
Contributor
Last seen
October 20, 2010
183
Apr 16, 2010 at 09:36 AM
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
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.
I'll have a look and get back to you.
Apr 19, 2010 at 05:46 AM
Apr 19, 2010 at 05:52 AM