Conditional Format - formula = 0 vs. entering zero
 

I need to distinguish between a formula that results in zero vs. entering a
zero. I currently have the foreground colour set to white when the formula
results in zero and black when it is non-zero.

Now my client wants to be able to enter a zero, overwriting the formula and
have it show up in black. I can't figure out how to make it work. Any ideas?

TIA rasinc

Conditional Format - formula = 0 vs. entering zero
 

Sorry, Excel 2000 but client is using Excel 2003 I believe. So far
everything has been interchangeable.

"rasinc" wrote:

I need to distinguish between a formula that results in zero vs. entering a
zero. I currently have the foreground colour set to white when the formula
results in zero and black when it is non-zero.

Now my client wants to be able to enter a zero, overwriting the formula and
have it show up in black. I can't figure out how to make it work. Any ideas?

TIA rasinc

Conditional Format - formula = 0 vs. entering zero
 

Hi rasinc,

Select cell A1. Use the menu item Insert - Name - Define
In the Names box enter: IsFormula
In the Refers to box enter: =GET.CELL(48, Sheet1!A1)
Click Add, then Close.

It is important to enter a relative reference, not absolute. The IsFormula
name then refers to its own cell. Note that Excel will add the sheet name in
the GET.CELL formula, so you can only use this technique on one sheet.

Now highlight your range and enter conditional formatting with the condition
Formula Is: =IsFormula

Note that GET.CELL is an XLM macro command, so you may get a warning message
when opening the workbook.

Ed Ferrero
www.edferrero.com

Conditional Format - formula = 0 vs. entering zero
 

Ed,

Can you use Get.Cell directly in a cell like this in B1
=SUMPRODUCT(--(XLM.GET.CELL(18,C1:C10)="Arial"))
If yes, then how?

"Ed Ferrero" wrote:

Hi rasinc,

Select cell A1. Use the menu item Insert - Name - Define
In the Names box enter: IsFormula
In the Refers to box enter: =GET.CELL(48, Sheet1!A1)
Click Add, then Close.

It is important to enter a relative reference, not absolute. The IsFormula
name then refers to its own cell. Note that Excel will add the sheet name in
the GET.CELL formula, so you can only use this technique on one sheet.

Now highlight your range and enter conditional formatting with the condition
Formula Is: =IsFormula

Note that GET.CELL is an XLM macro command, so you may get a warning message
when opening the workbook.

Ed Ferrero
www.edferrero.com

Conditional Format - formula = 0 vs. entering zero
 

Hi Sheeloo,

Can you use Get.Cell directly in a cell like this in B1
=SUMPRODUCT(--(XLM.GET.CELL(18,C1:C10)="Arial"))

No - you cannot :(

Ed Ferrero
www.edferrero.com

Conditional Format - formula = 0 vs. entering zero
 

Thanks but I have no idea what this does. Unfortunately, even though I tried
it exactly as you did, it exposed the zeros currently in the fields. For
example, I have a formula in S1232

=IF(R1232=0,0,R1232*U1232)

It results in a zero unless something is in column R. Right now the font in
column S is white when the cells in Column S hold a zero, black when anything
else. But my client was to be able to specifically enter a zero in the S
column and have it show as black.

Hope this helps explain it further.

"Ed Ferrero" wrote:

Hi rasinc,

Select cell A1. Use the menu item Insert - Name - Define
In the Names box enter: IsFormula
In the Refers to box enter: =GET.CELL(48, Sheet1!A1)
Click Add, then Close.

It is important to enter a relative reference, not absolute. The IsFormula
name then refers to its own cell. Note that Excel will add the sheet name in
the GET.CELL formula, so you can only use this technique on one sheet.

Now highlight your range and enter conditional formatting with the condition
Formula Is: =IsFormula

Note that GET.CELL is an XLM macro command, so you may get a warning message
when opening the workbook.

Ed Ferrero
www.edferrero.com

Conditional Format - formula = 0 vs. entering zero
 

Hi rasinc,

Download a sample file from
http://www.edferrero.com/content/ConditionalFormat.xls

to see how to apply GET.CELL to a conditional format.

Ed Ferrero
www.edferrero.com

Thanks but I have no idea what this does. Unfortunately, even though I
tried
it exactly as you did, it exposed the zeros currently in the fields.

Conditional Format - formula = 0 vs. entering zero
 

Hi Ed,

Sorry for taking so long to get back to you. I was sidetracked on other
jobs. I have just gone through several variations of what you put in the
spreadsheet and it works quite well. I liked the idea of redoing the
background pattern as well. This makes the cell without the formula more
evident and clear to the users.

I appreciate the help. I never would have solved this one on my own as I
have never used the Get.Cell(x) functions before. I did find a couple of
references to Get.Cell(42) and Get.Cell(20) as well however, I can't find
anything searching on the MS website that gives me definitions of the
command. Any chance you know where that is? I usually like to read up on
these things so if I need them in the future, I will have an idea of what to
use.

Thanks again.

"Ed Ferrero" wrote:

Hi rasinc,

Download a sample file from
http://www.edferrero.com/content/ConditionalFormat.xls

to see how to apply GET.CELL to a conditional format.

Ed Ferrero
www.edferrero.com

Thanks but I have no idea what this does. Unfortunately, even though I
tried
it exactly as you did, it exposed the zeros currently in the fields.

Conditional Format - formula = 0 vs. entering zero
 

I can't find anything searching on the MS
website that gives me definitions of the command.

There is a help file at this link:

http://www.microsoft.com/downloads/d...displaylang=EN

--
Biff
Microsoft Excel MVP


"rasinc" wrote in message
...
Hi Ed,

Sorry for taking so long to get back to you. I was sidetracked on other
jobs. I have just gone through several variations of what you put in the
spreadsheet and it works quite well. I liked the idea of redoing the
background pattern as well. This makes the cell without the formula more
evident and clear to the users.

I appreciate the help. I never would have solved this one on my own as I
have never used the Get.Cell(x) functions before. I did find a couple of
references to Get.Cell(42) and Get.Cell(20) as well however, I can't find
anything searching on the MS website that gives me definitions of the
command. Any chance you know where that is? I usually like to read up on
these things so if I need them in the future, I will have an idea of what
to
use.

Thanks again.

"Ed Ferrero" wrote:

Hi rasinc,

Download a sample file from
http://www.edferrero.com/content/ConditionalFormat.xls

to see how to apply GET.CELL to a conditional format.

Ed Ferrero
www.edferrero.com

Thanks but I have no idea what this does. Unfortunately, even though I
tried
it exactly as you did, it exposed the zeros currently in the fields.

Conditional Format - formula = 0 vs. entering zero
 

Thanks

"T. Valko" wrote:

I can't find anything searching on the MS
website that gives me definitions of the command.

There is a help file at this link:

http://www.microsoft.com/downloads/d...displaylang=EN

--
Biff
Microsoft Excel MVP


"rasinc" wrote in message
...
Hi Ed,

Sorry for taking so long to get back to you. I was sidetracked on other
jobs. I have just gone through several variations of what you put in the
spreadsheet and it works quite well. I liked the idea of redoing the
background pattern as well. This makes the cell without the formula more
evident and clear to the users.

I appreciate the help. I never would have solved this one on my own as I
have never used the Get.Cell(x) functions before. I did find a couple of
references to Get.Cell(42) and Get.Cell(20) as well however, I can't find
anything searching on the MS website that gives me definitions of the
command. Any chance you know where that is? I usually like to read up on
these things so if I need them in the future, I will have an idea of what
to
use.

Thanks again.

"Ed Ferrero" wrote:

Hi rasinc,

Download a sample file from
http://www.edferrero.com/content/ConditionalFormat.xls

to see how to apply GET.CELL to a conditional format.

Ed Ferrero
www.edferrero.com

Thanks but I have no idea what this does. Unfortunately, even though I
tried
it exactly as you did, it exposed the zeros currently in the fields.

Conditional Format - formula = 0 vs. entering zero
 

Hi rasinc,

I appreciate the help. I never would have solved this one on my own as
I
have never used the Get.Cell(x) functions before. I did find a couple
of
references to Get.Cell(42) and Get.Cell(20) as well however, I can't
find
anything searching on the MS website that gives me definitions of the
command. Any chance you know where that is?

Here are a couple of links that should help

http://office.microsoft.com/en-us/ex...475331033.aspx

http://support.microsoft.com/kb/128185/EN-US/

Ed Ferrero