yogi_Conditional Formatting For Items With Materials Used With No Material Price Listed For Open Ended List of Materials

                    Google Spreadsheet   Post  #1773
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep-26-2014
post by  robodawg42:
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/TwxMuRFJ2Yw)
How do I use conditional formatting governed by multiple cells?

Ok, the formula you listed 

=and(E3>0,E$1=0)+and(F3>0,F$1=0)+and(G3>0,G$1=0)

will definitely work, and I can change the range to every cell in the column. However, I was hoping there was a way to do this without writing out every and statement for each row (there are over 30). It's a momentary pain, but I was hoping for a more graceful solution, possibly changing it to something like:


=and(E3:G3>0,E$1:G$1=0)I know that way specifically won't work, but is there some way to go about it that I can use the and statement over the range instead of each individually?
-----------------------------------------------------------------------------------------------------------------------------------
following is the solution to a bit more generalized problem using open-ended list of Materials