From: LSG on 18 Mar 2010 17:59 Well I was able to replicate your solution and it worked! (took me a bit) Now I have to see if it will work with my project, but it definitely points me in the right direction! Thanks! -- -Liz "Squeaky" wrote: > Hi Liz, > > It is possible, but you have to have a way for an ingredient to trigger its > formatting i.e. how will flour “know” which recipe it is used in? > > What you can do is set up a chart in some hidden rows or off to the side. > This is how I set it up. Do it like this and then you can move things around > as you want. > I put the following list of ingredients starting in A3 down to A10. A1 is > the cell I will put the cake name in. > Flour > Eggs > Milk > Cream > Butter > Sugar > Chips > Vanilla > > I left column B blank. In C1 I placed Choc Cake, in D1 I placed Pound Cake, > in E1 I placed Round Cake. These are your cake headings and will run across > in the first row. Place an “x” in the cell across from the ingredient under > the cake heading. > It will look like this: > > (cake name) Choc cake pound cake round cake > Flour x x > Eggs x > Milk x > > And so on. (hopefully the x's line up here) > Now for the formulas. > In cell G3 put this formula: > > =IF(AND(C3="x",\$A\$1=C\$1)=TRUE,1,0) > > Watch the proper placement of the \$. > > Drag/copy it down to the bottom of your list, and over as many cells as you > have cake types. In my case I will drag it down to row 12, and across 5 > columns. (You can drag it down and across more if you anticipate more or > future ingredients and cake types.) > Now let's say you dragged your formulas across 5 columns from G to K. In F3 > put: > > =sum(g3:k3) Drag that down to the bottom of your chart. > > Place a cake name in A1, and wherever you have an x under that cake you will > see an x in Column F. > Now for the conditional formatting: > Select cell A3 and select conditional formatting. In the formula put: > =f3=0 > In the formatting part select a gray background and gray lettering, or > whatever you wish. > Ok and close. > Again select A3 then select Format Painter (Home Tab, Clipboard, with 2007) > and then select A4 to A10 to auto format those cells. > The ingredients with an “x” should be visible, with the others grayed out. > > If you make a list of cakes in another column you can place a drop down box > in and link it to A1. Use an active x control box. > > Squeaky First  |  Prev  |