 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / March 2006Conditional formatting - Find common material
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 2 Hi! It looks like if a particular row from I:U does not contain a zero then apply the color. Is that correct? Biff > Hi Guys, > [quoted text clipped - 16 lines] > 14-1234-56 no color 0 2 4 2 > 14-1234-56 no color 0 2 2 2 thats rite.. OK....... Select the range of cells in column G. I'll assume that's G3:G10. Goto Format>Conditional Formatting Formula is: =COUNTIF($I3:$U3,0)=0 Click the Format button Select the desired style(s) OK out Biff > thats rite.. 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. > 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. 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. Thanks Try this: =SUMPRODUCT(--(MOD(COLUMN(I4:U4),2)=1),--(I4:U4>0))=7 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 [quoted text clipped - 4 lines] > 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. Thanks THanks a million that is what i am looking for. Could you guide me how this formula works? > THanks a million that is what i am looking for. Could you guide me how > this formula works? Sure: =SUMPRODUCT(--(MOD(COLUMN(I4:U4),2)=1),--(I4:U4>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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.