Conditional Format Formula

Hi,

Attached is a sample file with a dummy data and the result as I need it.

I'm looking for a formula to highlight Green the cells when an ORDER (column "A") has ALL it`s items whit the STATUS "READY" (Column "D").

Could you help ?

Regards,
Roberto.
SAMPLE-FILE.xls
LVL 5
PabilioAsked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
Most Excel 2003 formulas (of the sort you need here) will be unable to reference the whole column. You can use a formula that would look at most of the column, but it's best to restrict it as much as you can. This will work in Excel 2003

=SUM((A$1:A$50000=A1)*(D$1:D$50000<>"ready"))=0

see attached

If you need more rows you can use all but one row of the spreadsheet, e.g. A1:A65535

regards, barry
SAMPLE-FILE-barry.xls
0
 
etech0Commented:
Put this formula into another column:
=COUNTIFS(A:A,A4,D:D,"PRODUCTION")

Then use conditional formatting to highlight any rows where that columns value is zero.

Do you know how to do that?
0
 
Shanan212Commented:
=COUNTIF($A$4:$A$33,$A4)=COUNTIFS($D$4:$D$33,"READY",$A$4:$A$33,$A4)

Select cell A4 and set conditional formatting with formula using the above
Then copy the cell A4's formats to rest of the cells (on A column)
See attached for example
SAMPLE-FILE.xls
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
barry houdiniCommented:
Perhaps turn that round a little - you can do as Shannan212 suggests but with this shorter formula

=COUNTIFS(A:A,A4,D:D,"<>ready")=0

regards, barry
0
 
PabilioAuthor Commented:
Hi,


I'm sorry that I did not give you some important details:

- We are using Excel 2003

- The CF should be for all Column "A" due that data comes from a Sql Database and it change a lot.

Due to this two conditions I can not use COUNTIFS or SUMIFS and (I believe) neither SUMPRODUCT

Any ideas ?

Thank you for your time.
Regards,
Roberto.
0
 
PabilioAuthor Commented:
Thank you very much Barry...

I restricted the formula to 10,000 Rows...The formula works perfectly and it does not slow down the sheet calculations.

If is ok with you, I'll give the chance to the other experts in case they have a different solution.

Regards,
Roberto.
0
 
barry houdiniCommented:
No problem

There are several similar ways I know of, e.g. this version with MATCH will also work

=ISNA(MATCH(1,(A$1:A$10000=A1)*(D$1:D$10000<>"ready"),0))

regards, barry
0
 
PabilioAuthor Commented:
Thank you very much Barry.
I wish you have a great and happy new year.
Regards,
Roberto.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.