Sep 22 2020 01:15 AM
Good morning All,
I am trying to get Excel to look at a list from column AC and then if the name is in the list and also in column E (inputted from a drop down list) it colours the cell in column G red. If the name in column E isn't in column AC then I would like the cell in column G to display "No P/O Req"
Also, once something is then put into column G I would like the cell to no longer be red.
We are trying to get our worksheet to display a red cell when a customer requires a purchase order number and not be red when we enter a purchase order number hence leaving a visible guide as to which jobs need purchase order numbers in them.
If anyone can advise the best formula to use for this that would be most appreciated - I have attached the sheet I am using
Thank you
Jim
Sep 22 2020 01:34 AM
As for the conditional formatting you may apply the rule with formula as
As for the text in cell that's by formula, not by conditional formatting. If to add, when one more condition for the formula.
Sep 22 2020 02:29 AM
Thank you for the reply. I applied the conditional format rule but the cells do not go red if the name is in column AC
I wasn't sure what you meant by As for the text in cell that's by formula, not by conditional formatting. If to add, when one more condition for the formula.
Sep 22 2020 02:43 AM
To which range did you apply the rule? And does it work as desired in file I attached?
Sep 22 2020 02:52 AM
I'm sorry, didn't realise you had sent the attachment back - just loaded it and that's working perfectly
thank you so much - been trying to do this for days :)
Sep 22 2020 03:16 AM
Hi Sergei, When I add in the rest of our data to the accounts tab and then validate the data to the drop down list the function no longer works, do you know why this is?
Sep 22 2020 04:52 AM
You mean conditional formatting doesn't work or something else? I added data close to the end, it works. Try to check condition manually at any empty cell
as
Sep 22 2020 05:13 AM
It seems to be ok at the top of the sheet but not at the bottom, I tried as you suggested and I get this result
It also doesn't work at the top of the sheet for the second entry of the same name. Does it work for you on yours if you do multiple entries of the same name ?
Sep 22 2020 06:07 AM
That only means the values in columns E and AC are different. Perhaps you have extra spaces in texts or other non-printable characters. Happens quite often if copy / paste from others source, especially from Web. Hard to say without seeing concrete values.
In any case, that's not conditional formatting error, that's data is not cleaned.
Sep 22 2020 06:13 AM
I'm sure you are right but they look the same, even copied and pasted it so it has to be :)
On your end if you put in Able Cleaning into cell E13 it comes up as red in cell G13 (i can see that)
If you put in Able Cleaning into cell E14 does cell G14 fill in red ? Cos mine doesn't
Sep 22 2020 06:52 AM
Sep 22 2020 07:00 AM
Ah I see, the list in AC column will only ever have the name in once in the list. So you are saying I need to put the name in column AC and in column E for the cell in column G to go red ?
I would like to put about 30 Names in Column AC and every cell in column E to make column G go red when it matches, sorry didnt explain myself very well
Is that possible ?
Sep 22 2020 07:12 AM
Yes, that's misunderstanding. To be sure - you have for example Able Cleaning only in AC13, and if you have Able Cleaning in E13, E28, E707 all G13, G28, G707 shall be highlighted, correct?
Sep 22 2020 07:15 AM
Yes that's what I need. Some accounts book 30-40 times a month and I need all their bookings to highlight in column G even if only listed once in Column AC
Same with all the other accounts too
Thank you so much for your help, really appreciate this
Sep 22 2020 07:27 AM
When rule formula will be
=COUNTIF($AC$2:$AC$982,E2)*($E2<>"")
it looks like
In attached file I hided some columns, you may unhide them back after check the data.
Sep 22 2020 07:57 AM
Your a genius :) Thank you
Just one thing, is it easy to make the red disappear when we then type in it ? (it did on the first one)
Doesn't matter though, really happy with this, thank you so much !
Sep 22 2020 08:05 AM
Sorry, forgot about this. Formula will be
=COUNTIF($AC$2:$AC$982,E2)*($E2<>"")*(G2="")
works as
Sep 22 2020 08:21 AM
Sep 22 2020 03:20 PM
@Jimbobmcwalton , you are welcome, glad to help
Sep 24 2020 02:08 AM
Hi Sergei,
Me again :) - Would you know the best formula to use to calculate the (count) of the red cells that generate in column G - I would like to have a cell that dynamically displays this count.
Cheeky I know but I presume this is an easy thing to do for you
I tried =@ColorFunction(AC12,$G$2:$G$981) but I am getting an error #NAME?
AC12 was my control cell in this formula btw.