Alert Errors need to be either Blank or "$0.00"

[Roddy]

I'm designing an invoice worksheet in Excel 2002 for a physical therapy
office. There are numerous calculations since this is an invoice. I'm trying
to have the Alert Errors (these are really the only 2 errors that show up:
#DIV/0!, #NAME?) to be either Blank or "$0.00". I've read through several
help files on the internet and here and was using Conditional Formatting so
that the error cells would be white, blue, or yellow (depending on the color
of the cell in question). The problem is when there is no error, the results
can't be seen in some cells because of the formatting, but does appear in
others.

Below is an example of what I've done.

1) When trying to hide the Alert Errors in certain cells (some have
formulas/calculations, totals,etc.), I found in several posts to just go to
Conditional Formatting→Formula Is | =ISERROR(cell reference)→Format→then
choose white or a color matching the cell color for the text. When a cell has
no entry and the Alert Error appears (such as: #DIV/0! or #NAME?), the
Conditional Formatting with the color is not a problem, but when there is a
total or value in that cell, then it can't be seen because of the Conditional
Formatting. Is there a SIMPLE way to just have select cells with the Alert
Error to be blank (while still keeping the cell color if it has one) when
there is an Alert Error, and others I choose to show "$0.00"?

2) Once the above has been accomplished, is there an easy and efficient way
to incorporate these Excel Sheets into a database, while still keeping all
the formatting, calculations, cell colors, column placements, text
blocks/titles, and the actual look of the way it is designed graphically?

 

[Kassie]

I would never use conditional formatting to hide errors!?

If you want to multiply or add or whatever the contents of 2 cells, and the
possibility exists that one of them might be empty, thus resulting in eg a
#DIV/0, the best way is to trap the error with an IF statement. Say you
formula currently reads =F2*B2. Change it to read
=IF(OR(F2="",B2=""),"",F2*B2). This checks whether any of the cells are
empty, if true, then sets the answer to "", but otherwise does the
calculation. You can also substitute the "" with 0 (zero), if you would
prefer to show a zero.

If you set up your invoice like this, it means that you can enter formulae
like the above in all the lines you want to be able to generate. You are
presented with a clean and neat invoice, but the moment you enter enough data
on a specific line, you formulae come to life.

When you say you want to incorporate these sheets into a database, what do
you want to achieve? Do you want each invoice, as it is generated, to be
copied to a database showing items / services invoiced? That is normally
achieved with a print button, to activate a macro which would copy the
number, date, customer details, and each line of the invoice to said
database. I normally at this time increment the number and save the invoice
as it's number as well, which means that my template always remains clean.
Contact me if you want a sample of such a template I created for a
dressmaker, obviously different, but which could give you an idea of what to
do.

 

[Roddy]

Kassie,
Thanks for the formula, but I'm still having difficulty. The reason is that
most of the cells that have the alert errors, are cells with formulas such
as: =SUM(range of cells divided by a cell) or multiplied, etc. You get the
idea. Then, if 2 cells need to be calculated with the result in another cell
as the total or line total up to that point, and one of those cells used in
the calculation is either left blank or is a zero,
the error still shows.
Sorry this is complicated for me.

 

[Kassie]

OK, wherever you could possibly pick up such errors, insert the following
between the "=" sign and you formula. IF(ISERROR( Now after your original
formula, insert a closing bracket, a comma, insert 2 double quotes, another
comma, and then repeat your original formula, followed by another closing
bracket.

Say you had a formula =B6/K6+L6. This will result in an error when one cell
is empty. Now change this formula to read

=IF(ISERROR(B6/K6+L6),"",B6/K6+L6)

If you always insert formulae like this, or like the previous example, you
will not have this kind of problem.

Otherwise, Isuggest you send me your file, and I'll sort it out for you
--

HTH

Kassie

Replace xxx with hotmail

 

[Roddy]

Kassie,
Thanks soooo much. I realized with your formula that my error was that I was
still adding the word "SUM" before my formula as it originally was. I
eliminated it, and it worked fine. Bless you.

As to my wanting to incorporate all these spreadsheets into a database, let
me see if I can explain it better.

At the moment, a spreadsheet file is entered with all the necessary
information, but then it is saved per patient, and per day of visit. For
example, let's say you go in today, 3/30/2009, for a back adjustment, then
this Wednesday 4/1/2009 to buy a nutrition DVD. At the moment, the
spreadsheet file that I've been working on is opened, and once the info has
been entered, it is "Saved As.." with your name: Kassie, and the date. In
this example, you can see that there would be 2 files just for you, just for
these 2 visits. Multiply that times several hundred other patients, and you
can see how huge the folder holding all of these is getting.
The problem with Access is that it doesn't (at least I haven't figured out
how) really allow a simple way to keep the exact Excel visual representation
along with all of the spreadsheet's formulas. Really, I wish I could just
take the spreadsheet files as they are, and archive them in a simple
database. This way, I can pull up by patient, or by date, or procedure, etc.
Is it possible? I've searched for answers, but the examples I keep finding
are when the Excel worksheet is in a basic Column heading, and basic row info
(basic X-Y form, or A..B..C.. being column headings, and the numbered rows
the info.) not how my complex one is.
Let me know please, and I really appreciate all you've done so far for me.

 

[Kassie]

Hi Roddy

Glad I could be of help.

What I envisage, is one master file for all patients, in which you would log
each visit/treatment/consultation. With the aid of a macro, the information
entered in this file, would then be copied to the relevant patient's own
file, to keep a chronological record of such instances in each patient's own
file.

Yes, you would have to look at layout, in any case to enable any logical
form of record keeping, but invoices and suck like can be set up as you
prefer.
Opgaaf van bestellings reeds gelewer
Invoice | Date|Patient|Description|Units|Cost Unit|Tax|Total
could be some of the headings.
Under this, each visit would be recorded horizontally, although in the
invoice it may look different.

I would again extend my offer to you, to send you an example of just such a
file, which generates an invoice, but at print time also fille out the
patient record based on the invoice. If you are interested, contact me at my
email adress, and I'll forward it to you.

Talking of Access, have you tried a table view to view your
records?

--

HTH

Kassie

Replace xxx with hotmail