formatting

P

pioneermusic55

i am creating a spreadsheet with a lot of cell references for billing of
insurance purposes. Basically, what i want to know is this:

I have formatted cells to be referenced to another worksheet in the
workbook, and right now, if there is no value for the date, it shows
1/0/1900, but if there is a date, then it shows the correct date. I
want to know if there is a way to make an IF statement for formatting
so that if there is a date it shows it, and if not, it shows a dash or
0 or nothing at all. please let me know:cool:
 
D

Dave Peterson

Same thing happens when you use a cell in the same worksheet:

=a1
will return a 0 if A1 is empty.

The usual fix is to change the formula:
=if(a1="","",a1)

So just change your formula to check for empty first.
 
P

pioneermusic55

i cannot get this to work correctly...it still gives me the same
problem. I need this to work correctly. Can i send you the file and
have you work with it and send it back? :(
 
D

Dave Peterson

No.

But you can share your existing formula.
i cannot get this to work correctly...it still gives me the same
problem. I need this to work correctly. Can i send you the file and
have you work with it and send it back? :(
Click to expand...
 
P

pioneermusic55

ok,

I have workbook that is sectioned according to jobsite. Then sectione
according to Insurance Calculations, Billing, and Insurance Claims. So
i have 3 tabs for each jobsite. My problem is this:

Everywhere there isnt a value, i have zeros. I dont want zeros.
tried to work with the If statements, but that didnt work. Here ar
some formulas.

=IF(J9>0,IF(I9>$C$3,J9-I9+1,IF(J9<$C$3,0,J9-$C$3+1)),IF(I9=0
,IF(I9<$C$3,$E$3-$C$3+1,$E$3-I9+1)))

that one is an if statement that relies on a cell that has a date an
if the date is greater then that date, it bills according to the numbe
of days that its greater than, but if its false, then it bills accordin
to another date cell, either zero, or full billing. Really, i didn
design that formula myself.

That one is fine to have zeros on, its the next list of formulas that
want if statements on, so zeros dont show up.

='MT. HOPE BILLING'!D8
='MT. HOPE BILLING'!G8
=RICKERS!G119

etc...


i appreciate any help you can give me, :
 
D

Dave Peterson

This formula:
='MT. HOPE BILLING'!D8
would become:
=if('MT. HOPE BILLING'!D8="","",'MT. HOPE BILLING'!D8)

And the same kind of thing for the others.
 
P

pioneermusic55

i tried that formula, and it gave me zeros, and it didnt keep any of th
values it was supposed to keep.

('MT. HOPE BILLING'!D8="","",'MT. HOPE BILLING'!D8) didnt work exactly



this spreadsheet is turning into a nightmare. :confused
 
D

Dave Peterson

I don't see the =if() portion of the formula.

And if your formula was actually correct, then I'm guessing that 'mt. hope
billing'!d8 actually contains a 0.
 
P

pioneermusic55

still no luck... it still places zeros even after i made sure the
werent in the original worksheet. Basically its a big tangled mess o
references and such. I dont know what else there is to do... :
 
D

Dave Peterson

Are you sure that the value in that cell is really 0?

Maybe it's just a little more or less than 0 and the way it's formatted hides
its real value.

If you select the cell with the formula, then hit F2, then F9, what do you see
in the formula bar?

(Hit edit|Undo to put the formula back.)
 
Top