Help on lookup from a list and then display result based on content

Brass Contributor

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  

 

 

24 Replies

@Jimbobmcwalton 

As for the conditional formatting you may apply the rule with formula as

image.png

As for the text in cell that's by formula, not by conditional formatting. If to add, when one more condition for the formula.

@Sergei Baklan 

 

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.

@Jimbobmcwalton 

To which range did you apply the rule? And does it work as desired in file I attached?

@Sergei Baklan 

 

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 :)

@Sergei Baklan 

 

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?

@Jimbobmcwalton 

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

image.png

@Sergei Baklan 

 

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 

 

Jimbobmcwalton_0-1600776688931.png

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 ?

 

 

@Jimbobmcwalton 

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. 

@Sergei Baklan 

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 

@Jimbobmcwalton 

Nope since AC14 is empty

image.png

 

If AC14 has the same value - yes, it's red

image.png

@Sergei Baklan 

 

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 ?

@Jimbobmcwalton 

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?

@Sergei Baklan 

 

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 

@Jimbobmcwalton 

When rule formula will be

=COUNTIF($AC$2:$AC$982,E2)*($E2<>"")

it looks like

image.png

In attached file I hided some columns, you may unhide them back after check the data.

@Sergei Baklan 

 

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 !  

@Jimbobmcwalton 

Sorry, forgot about this. Formula will be

=COUNTIF($AC$2:$AC$982,E2)*($E2<>"")*(G2="")

works as

image.png

@Sergei Baklan 

 

Don't apologise - you are amazing - thank you again - working perfectly :) 

@Sergei Baklan 

 

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.