software

Hi Guys,

What i am trying to do is to to determine the common material that is
used among different model od product in a product family. I have the
column C the various part number for the product family. Each product
model is made up of different combination of the parts.

In I3:U3 i have the model number for each product. Under each are the
combination of various part that make up each model. What i need to do
is in column G conditional formatiing that if all the different model
use a particular part (part number). The respective cell in column in
the row will be color. This will help me to determine what are the
parts that are common to all the product. Pls see below. Many thanks.

Column C Column G Column I .........................Column U
Part no Common Product 1 Product 2 Product 3 Product 4
12-1234-56 no color 1 4 0 6
13-2345-45 color 2 3 2 2
14-1234-56 no color 0 2 4 2
14-1234-56 no color 0 2 2 2Hi!

It looks like if a particular row from I:U does not contain a zero then
apply the color. Is that correct?

Biff

quot;kuanshengquot; gt; wrote in message ups.com...
gt; Hi Guys,
gt;
gt; What i am trying to do is to to determine the common material that is
gt; used among different model od product in a product family. I have the
gt; column C the various part number for the product family. Each product
gt; model is made up of different combination of the parts.
gt;
gt; In I3:U3 i have the model number for each product. Under each are the
gt; combination of various part that make up each model. What i need to do
gt; is in column G conditional formatiing that if all the different model
gt; use a particular part (part number). The respective cell in column in
gt; the row will be color. This will help me to determine what are the
gt; parts that are common to all the product. Pls see below. Many thanks.
gt;
gt; Column C Column G Column I .........................Column U
gt; Part no Common Product 1 Product 2 Product 3 Product 4
gt; 12-1234-56 no color 1 4 0 6
gt; 13-2345-45 color 2 3 2 2
gt; 14-1234-56 no color 0 2 4 2
gt; 14-1234-56 no color 0 2 2 2
gt;
thats rite..OK.......

Select the range of cells in column G. I'll assume that's G3:G10.
Goto Formatgt;Conditional Formatting
Formula is: =COUNTIF($I3:$U3,0)=0
Click the Format button
Select the desired style(s)
OK out

Biff

quot;kuanshengquot; gt; wrote in message oups.com...
gt; thats rite..
gt;
What i need is that if a particular row from I:U all the cell must not
contain zero or blank then color. I was wrong earlier.
quot;kuanshengquot; gt; wrote in message ups.com...
gt; What i need is that if a particular row from I:U all the cell must not
gt; contain zero or blank then color. I was wrong earlier.

OK, change the formula to:

=AND(COUNTIF($I3:$U3,0)=0,COUNTBLANK($I3:$U3)=0)

Biff
This didnt work. In column I:U, column I, K, M, O, Q, S and U. I will
color cell in the respective row of column G. only if in all cell of a
row of I, K, M, O, Q, S and U contain a numeric value except a zero or
blank. What i meant is that in for example in row 4. All the cell I4,
K4, M4, O4, Q4, S4 and U4 all must contain a numeric value except a
zero or blank, then the respective cell in column G (G4) will be color.
This is used to indicate that all the different product uses this part
thus is common to all. Sorry for all the trouble i have caused. ThanksTry this:

=SUMPRODUCT(--(MOD(COLUMN(I4:U4),2)=1),--(I4:U4gt;0))=7

Biff

quot;kuanshengquot; gt; wrote in message oups.com...
gt; This didnt work. In column I:U, column I, K, M, O, Q, S and U. I will
gt; color cell in the respective row of column G. only if in all cell of a
gt; row of I, K, M, O, Q, S and U contain a numeric value except a zero or
gt; blank. What i meant is that in for example in row 4. All the cell I4,
gt; K4, M4, O4, Q4, S4 and U4 all must contain a numeric value except a
gt; zero or blank, then the respective cell in column G (G4) will be color.
gt; This is used to indicate that all the different product uses this part
gt; thus is common to all. Sorry for all the trouble i have caused. Thanks
gt;
THanks a million that is what i am looking for. Could you guide me how
this formula works?
quot;kuanshengquot; gt; wrote in message oups.com...
gt; THanks a million that is what i am looking for. Could you guide me how
gt; this formula works?

Su

=SUMPRODUCT(--(MOD(COLUMN(I4:U4),2)=1),--(I4:U4gt;0))=7

Since the range you're interested in is every other column starting in
column I through column U, the MOD function checks the column number and
makes sure only the defined columns you want are included in the
calculation.

There are a total of 7 columns that fit the criteria so we need to check
that the total number of entries that meet the criteria equals 7.

Biff