A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Conditional Format for Blank Cells



 
 
Thread Tools Display Modes
  #1  
Old March 18th, 2010, 05:30 PM posted to microsoft.public.excel.worksheet.functions
LSG
external usenet poster
 
Posts: 17
Default Conditional Format for Blank Cells

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
  #2  
Old March 18th, 2010, 05:46 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Conditional Format for Blank Cells

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" wrote in message
...
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



  #3  
Old March 18th, 2010, 07:33 PM posted to microsoft.public.excel.worksheet.functions
LSG
external usenet poster
 
Posts: 17
Default Conditional Format for Blank Cells

(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" wrote in message
...
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



.

  #4  
Old March 18th, 2010, 08:58 PM posted to microsoft.public.excel.worksheet.functions
Squeaky
external usenet poster
 
Posts: 131
Default Conditional Format for Blank Cells

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
  #5  
Old March 18th, 2010, 10:38 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Conditional Format for Blank Cells

Try to explain what you want to do *based on the REAL application and using
REAL data*.

--
Biff
Microsoft Excel MVP


"LSG" wrote in message
...
(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" wrote in message
...
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



.



  #6  
Old March 18th, 2010, 10:59 PM posted to microsoft.public.excel.worksheet.functions
LSG
external usenet poster
 
Posts: 17
Default Conditional Format for Blank Cells

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:46 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.