Prev: VLOOKUP with HLOOKUP
Next: Sumproduct but only first time corresponding value is seen e.g. un
From: LSG on 18 Mar 2010 12:30
I am trying to figure out, if possible, to make certain blank cells in a
row/column to be filled-in (blacked out) if a certain cell contains a
particular text. For example:

Let's say I'm wanting to create a spreadsheet that will help me indicated,
from a list of ingredients in Column A, what ingredients to make for certain
cakes.

If Cell B1 Contains the word 'pound cake', then I wanted a range of cells
in that same column to shade out those ingredients that do not pertain to
making a pound cake.

Is this clear? Is this possible?? Thanks!
--
-Liz
From: T. Valko on 18 Mar 2010 12:46
>Is this possible??

Yes

>Is this clear?

Not exactly! How do you know what ingredients are or are not needed for
pound cake? Obviously, we (as humans) could look at a list of ingredients
and tell that chili powder is not an ingredient for making pound cake but
how do we let Excel know that?

So, you'd have to create a specific list of the ingredients for making pound
cake then compare that specific list to a more general list and then apply
the formatting to the ingredients on the general list that don't belong with
pound cake.

Or, you can make an ingredient "database" where you would type in pound cake
then all the ingredients for making pound cake would be listed.

--
Biff
Microsoft Excel MVP


"LSG" <LSG(a)discussions.microsoft.com> wrote in message
news:1416BC14-DC13-400F-9E4D-579B49F909A2(a)microsoft.com...
>I am trying to figure out, if possible, to make certain blank cells in a
> row/column to be filled-in (blacked out) if a certain cell contains a
> particular text. For example:
>
> Let's say I'm wanting to create a spreadsheet that will help me indicated,
> from a list of ingredients in Column A, what ingredients to make for
> certain
> cakes.
>
> If Cell B1 Contains the word 'pound cake', then I wanted a range of cells
> in that same column to shade out those ingredients that do not pertain to
> making a pound cake.
>
> Is this clear? Is this possible?? Thanks!
> --
> -Liz


From: LSG on 18 Mar 2010 14:33
(Sorry, it's hard to explain whats in my head! FYI: My project has nothing to
do with making cakes….it's more for auditing files for certain documents. The
cakes seemed easier to explain.)

Well the list of ingredients would be in Column A, more specifically
starting in A2, then if I select 'pound cake' in Cell B1 (from a pull-down
list), I would like for the cells in Column B next to the ingredients in
column A that DO NOT pertain to making a 'pound cake' to fill-in/shade black
or perhaps display 'n/a'. I know that I would need to make conditions in each
cell in Column B (next to the ingredients in Column A). The question is
exactly what formula/condition are they?

Could you please elaborate on the 'Database' idea? That sounds easier,
especially for updates. Where can I look to create that within excel?


Thanks for your help!

--
-Liz


"T. Valko" wrote:

> >Is this possible??
>
> Yes
>
> >Is this clear?
>
> Not exactly! How do you know what ingredients are or are not needed for
> pound cake? Obviously, we (as humans) could look at a list of ingredients
> and tell that chili powder is not an ingredient for making pound cake but
> how do we let Excel know that?
>
> So, you'd have to create a specific list of the ingredients for making pound
> cake then compare that specific list to a more general list and then apply
> the formatting to the ingredients on the general list that don't belong with
> pound cake.
>
> Or, you can make an ingredient "database" where you would type in pound cake
> then all the ingredients for making pound cake would be listed.
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "LSG" <LSG(a)discussions.microsoft.com> wrote in message
> news:1416BC14-DC13-400F-9E4D-579B49F909A2(a)microsoft.com...
> >I am trying to figure out, if possible, to make certain blank cells in a
> > row/column to be filled-in (blacked out) if a certain cell contains a
> > particular text. For example:
> >
> > Let's say I'm wanting to create a spreadsheet that will help me indicated,
> > from a list of ingredients in Column A, what ingredients to make for
> > certain
> > cakes.
> >
> > If Cell B1 Contains the word 'pound cake', then I wanted a range of cells
> > in that same column to shade out those ingredients that do not pertain to
> > making a pound cake.
> >
> > Is this clear? Is this possible?? Thanks!
> > --
> > -Liz
>
>
> .
>
From: Squeaky on 18 Mar 2010 15:58
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
From: T. Valko on 18 Mar 2010 17:38
Try to explain what you want to do *based on the REAL application and using
REAL data*.

--
Biff
Microsoft Excel MVP


"LSG" <LSG(a)discussions.microsoft.com> wrote in message
news:F6687BCE-DFA3-457E-8D8E-23C8FAB50036(a)microsoft.com...
> (Sorry, it's hard to explain whats in my head! FYI: My project has nothing
> to
> do with making cakes..it's more for auditing files for certain documents.
> The
> cakes seemed easier to explain.)
>
> Well the list of ingredients would be in Column A, more specifically
> starting in A2, then if I select 'pound cake' in Cell B1 (from a pull-down
> list), I would like for the cells in Column B next to the ingredients in
> column A that DO NOT pertain to making a 'pound cake' to fill-in/shade
> black
> or perhaps display 'n/a'. I know that I would need to make conditions in
> each
> cell in Column B (next to the ingredients in Column A). The question is
> exactly what formula/condition are they?
>
> Could you please elaborate on the 'Database' idea? That sounds easier,
> especially for updates. Where can I look to create that within excel?
>
>
> Thanks for your help!
>
> --
> -Liz
>
>
> "T. Valko" wrote:
>
>> >Is this possible??
>>
>> Yes
>>
>> >Is this clear?
>>
>> Not exactly! How do you know what ingredients are or are not needed for
>> pound cake? Obviously, we (as humans) could look at a list of ingredients
>> and tell that chili powder is not an ingredient for making pound cake but
>> how do we let Excel know that?
>>
>> So, you'd have to create a specific list of the ingredients for making
>> pound
>> cake then compare that specific list to a more general list and then
>> apply
>> the formatting to the ingredients on the general list that don't belong
>> with
>> pound cake.
>>
>> Or, you can make an ingredient "database" where you would type in pound
>> cake
>> then all the ingredients for making pound cake would be listed.
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "LSG" <LSG(a)discussions.microsoft.com> wrote in message
>> news:1416BC14-DC13-400F-9E4D-579B49F909A2(a)microsoft.com...
>> >I am trying to figure out, if possible, to make certain blank cells in a
>> > row/column to be filled-in (blacked out) if a certain cell contains a
>> > particular text. For example:
>> >
>> > Let's say I'm wanting to create a spreadsheet that will help me
>> > indicated,
>> > from a list of ingredients in Column A, what ingredients to make for
>> > certain
>> > cakes.
>> >
>> > If Cell B1 Contains the word 'pound cake', then I wanted a range of
>> > cells
>> > in that same column to shade out those ingredients that do not pertain
>> > to
>> > making a pound cake.
>> >
>> > Is this clear? Is this possible?? Thanks!
>> > --
>> > -Liz
>>
>>
>> .
>>


 |  Next  |  Last
Pages: 1 2
Prev: VLOOKUP with HLOOKUP
Next: Sumproduct but only first time corresponding value is seen e.g. un