Conditional Format - formula = 0 vs. entering zero

R

rasinc

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
 
R

rasinc

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

Ed Ferrero

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
 
S

Sheeloo

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?
 
R

rasinc

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.
 
R

rasinc

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Conditional Formatting 4
conditional format change 4
Conditional Formatting - Selective Cell Highlighting 5
Sumproduct with blanks and the numeric 0 3
Formula result not displaying correctly 2
cells with zero value are shown as blank, not zero 2
Tweak formula to not count blanks as 0 4
Excel 2003 IF formula returns non-zero value instead of zero 7

Top