Prev: VLOOKUP with HLOOKUP
Next: Sumproduct but only first time corresponding value is seen e.g. un
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  | 
Pages: 1 2
Prev: VLOOKUP with HLOOKUP
Next: Sumproduct but only first time corresponding value is seen e.g. un