On Wed, 25 Apr 2007 00:54:01 -0700, Frank Situmorang
wrote:

Ron:

In summary sheet, maybe I misnamed it, maybe I should have called it "just
another sheet for another presentation of data in which the date field for
each job number and sales amount number is presented.

This is my formula in my the date field of "just another sheet". In fact in
inv07, inv06, inv05 sheets the date is really blank for the empty date, but
how come it shows "0-Jan-00' in "just another sheet". Is that because of the
formula?
=IF(ISNA(VLOOKUP(H122,'C:\hfsfile\DEV\[NEW INVOICE LIST.xls]INVOICE
''07'!$A$7:$AJ$2500,31,FALSE)),IF(ISNA(VLOOKUP(H1 22,'C:\hfsfile\DEV\[NEW
INVOICE LIST.xls]INVOICE
''06'!$A$7:$AI$2500,31,FALSE)),VLOOKUP(H122,'C:\h fsfile\DEV\[NEW INVOICE
LIST.xls]INVOICE
''05'!$A$6:$AJ$2500,31,FALSE),VLOOKUP(H122,'C:\hf sfile\DEV\[NEW INVOICE
LIST.xls]INVOICE
''06'!$A$7:$AI$2500,31,FALSE)),VLOOKUP(H122,'C:\h fsfile\DEV\[NEW INVOICE
LIST.xls]INVOICE ''07'!$A$7:$AJ$2500,31,FALSE))

Thanks a lot.

Frank


Well, as I wrote, you could test your formula and, if it returns a zero value,
substitute a null string.

Or you could use conditional formatting to change the font of a zero to the
background color (nominally white) which would leave it as zero but make it
invisible.

The formula would look like:

=IF((IF(ISNA(VLOOKUP(H122,'C:\hfsfile\DEV\[NEW INVOICE LIST.xls]INVOICE
''07'!$A$7:$AJ$2500,31,FALSE)),
IF(ISNA(VLOOKUP(H122,'C:\hfsfile\DEV\[NEW INVOICE LIST.xls]INVOICE
''06'!$A$7:$AI$2500,31,FALSE)),
VLOOKUP(H122,'C:\hfsfile\DEV\[NEW INVOICE LIST.xls]INVOICE
''05'!$A$6:$AJ$2500,31,FALSE),
VLOOKUP(H122,'C:\hfsfile\DEV\[NEW INVOICE LIST.xls]INVOICE
''06'!$A$7:$AI$2500,31,FALSE)),
VLOOKUP(H122,'C:\hfsfile\DEV\[NEW INVOICE LIST.xls]INVOICE
''07'!$A$7:$AJ$2500,31,FALSE)))=0,"",
IF(ISNA(VLOOKUP(H122,'C:\hfsfile\DEV\[NEW INVOICE LIST.xls]INVOICE
''07'!$A$7:$AJ$2500,31,FALSE)),
IF(ISNA(VLOOKUP(H122,'C:\hfsfile\DEV\[NEW INVOICE LIST.xls]INVOICE
''06'!$A$7:$AI$2500,31,FALSE)),
VLOOKUP(H122,'C:\hfsfile\DEV\[NEW INVOICE LIST.xls]INVOICE
''05'!$A$6:$AJ$2500,31,FALSE),
VLOOKUP(H122,'C:\hfsfile\DEV\[NEW INVOICE LIST.xls]INVOICE
''06'!$A$7:$AI$2500,31,FALSE)),
VLOOKUP(H122,'C:\hfsfile\DEV\[NEW INVOICE LIST.xls]INVOICE
''07'!$A$7:$AJ$2500,31,FALSE)))


--ron