Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 65
Default Blank date column return datel 00-Jan-00

Hello:

Following is my formula to be put in data sheet ( destination sheet) while
New Invoice List is the name of my source sheet.

My problem is the blank date in the source sheet is shown as " 0-Jan-00",
while I want it to renturn just as source/ blank.

Please help me how can we make it , because in the summary sheet I want to
make the sum if blank or zero 0, will not be calculated.

=IF(ISNA(VLOOKUP(H6674,'[NEW INVOICE LIST.xlsb]INVOICE
''08'!$A$7:$AX$6000,32,FALSE)),IF(ISNA(VLOOKUP(H66 74,'[NEW INVOICE
LIST.xlsb]INVOICE ''07'!$A$7:$AX$6000,31,FALSE)),IF(ISNA(VLOOKUP(H66 74,'[NEW
INVOICE LIST.xlsb]INVOICE
''06'!$A$7:$AX$6000,31,FALSE)),IF(ISNA(VLOOKUP(H66 74,'[NEW INVOICE
LIST.xlsb]INVOICE ''05'!$A$7:$AX$6000,29,FALSE)),VLOOKUP(H6674,'[NEW INVOICE
LIST.xlsb]INVOICE ''05'!$A$7:$AX$6000,29,FALSE)),VLOOKUP(H6674,'[NEW INVOICE
LIST.xlsb]INVOICE ''06'!$A$7:$AX$6000,31,FALSE)),VLOOKUP(H6674,'[NEW INVOICE
LIST.xlsb]INVOICE ''07'!$A$7:$AX$6000,31,FALSE)),VLOOKUP(H6674,'[NEW INVOICE
LIST.xlsb]INVOICE ''08'!$A$7:$AX$6000,32,FALSE))

--
H. Frank Situmorang
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 169
Default Blank date column return datel 00-Jan-00

You could add another IF statement that says if the result = 0, then return
"" otherwise just execute the formula you just laid out.
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Frank Situmorang" wrote:

Hello:

Following is my formula to be put in data sheet ( destination sheet) while
New Invoice List is the name of my source sheet.

My problem is the blank date in the source sheet is shown as " 0-Jan-00",
while I want it to renturn just as source/ blank.

Please help me how can we make it , because in the summary sheet I want to
make the sum if blank or zero 0, will not be calculated.

=IF(ISNA(VLOOKUP(H6674,'[NEW INVOICE LIST.xlsb]INVOICE
''08'!$A$7:$AX$6000,32,FALSE)),IF(ISNA(VLOOKUP(H66 74,'[NEW INVOICE
LIST.xlsb]INVOICE ''07'!$A$7:$AX$6000,31,FALSE)),IF(ISNA(VLOOKUP(H66 74,'[NEW
INVOICE LIST.xlsb]INVOICE
''06'!$A$7:$AX$6000,31,FALSE)),IF(ISNA(VLOOKUP(H66 74,'[NEW INVOICE
LIST.xlsb]INVOICE ''05'!$A$7:$AX$6000,29,FALSE)),VLOOKUP(H6674,'[NEW INVOICE
LIST.xlsb]INVOICE ''05'!$A$7:$AX$6000,29,FALSE)),VLOOKUP(H6674,'[NEW INVOICE
LIST.xlsb]INVOICE ''06'!$A$7:$AX$6000,31,FALSE)),VLOOKUP(H6674,'[NEW INVOICE
LIST.xlsb]INVOICE ''07'!$A$7:$AX$6000,31,FALSE)),VLOOKUP(H6674,'[NEW INVOICE
LIST.xlsb]INVOICE ''08'!$A$7:$AX$6000,32,FALSE))

--
H. Frank Situmorang

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 65
Default Blank date column return datel 00-Jan-00

Ok Kan, now if I want to total all with the income date with the following
formula, does exel interpret it as 0 or zero??:

=SUM(IF(DATA!$F$11:$F$9098=A7,IF(DATA!$X$11:$X$909 8<0,IF(DATA!$X$11:$X$9098<"cancell",DATA!$AB$11: $AB$9098,0),0),0))

This range is the date column, which shows "0-Jan-00"

Thanks for your help
--
H. Frank Situmorang


"M Kan" wrote:

You could add another IF statement that says if the result = 0, then return
"" otherwise just execute the formula you just laid out.
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Frank Situmorang" wrote:

Hello:

Following is my formula to be put in data sheet ( destination sheet) while
New Invoice List is the name of my source sheet.

My problem is the blank date in the source sheet is shown as " 0-Jan-00",
while I want it to renturn just as source/ blank.

Please help me how can we make it , because in the summary sheet I want to
make the sum if blank or zero 0, will not be calculated.

=IF(ISNA(VLOOKUP(H6674,'[NEW INVOICE LIST.xlsb]INVOICE
''08'!$A$7:$AX$6000,32,FALSE)),IF(ISNA(VLOOKUP(H66 74,'[NEW INVOICE
LIST.xlsb]INVOICE ''07'!$A$7:$AX$6000,31,FALSE)),IF(ISNA(VLOOKUP(H66 74,'[NEW
INVOICE LIST.xlsb]INVOICE
''06'!$A$7:$AX$6000,31,FALSE)),IF(ISNA(VLOOKUP(H66 74,'[NEW INVOICE
LIST.xlsb]INVOICE ''05'!$A$7:$AX$6000,29,FALSE)),VLOOKUP(H6674,'[NEW INVOICE
LIST.xlsb]INVOICE ''05'!$A$7:$AX$6000,29,FALSE)),VLOOKUP(H6674,'[NEW INVOICE
LIST.xlsb]INVOICE ''06'!$A$7:$AX$6000,31,FALSE)),VLOOKUP(H6674,'[NEW INVOICE
LIST.xlsb]INVOICE ''07'!$A$7:$AX$6000,31,FALSE)),VLOOKUP(H6674,'[NEW INVOICE
LIST.xlsb]INVOICE ''08'!$A$7:$AX$6000,32,FALSE))

--
H. Frank Situmorang

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Blank date column return datel 00-Jan-00

Use another cell!

Use that current formula and "hide" it somewhere. Then use another formula
that tests that cell for 0.

Your long formula in cell A1.

Then test that cell:

=IF(A1=0,"",A1)

--
Biff
Microsoft Excel MVP


"M Kan" <tipsoftheweek at gmail dot com wrote in message
...
You could add another IF statement that says if the result = 0, then
return
"" otherwise just execute the formula you just laid out.
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Frank Situmorang" wrote:

Hello:

Following is my formula to be put in data sheet ( destination sheet)
while
New Invoice List is the name of my source sheet.

My problem is the blank date in the source sheet is shown as " 0-Jan-00",
while I want it to renturn just as source/ blank.

Please help me how can we make it , because in the summary sheet I want
to
make the sum if blank or zero 0, will not be calculated.

=IF(ISNA(VLOOKUP(H6674,'[NEW INVOICE LIST.xlsb]INVOICE
''08'!$A$7:$AX$6000,32,FALSE)),IF(ISNA(VLOOKUP(H66 74,'[NEW INVOICE
LIST.xlsb]INVOICE
''07'!$A$7:$AX$6000,31,FALSE)),IF(ISNA(VLOOKUP(H66 74,'[NEW
INVOICE LIST.xlsb]INVOICE
''06'!$A$7:$AX$6000,31,FALSE)),IF(ISNA(VLOOKUP(H66 74,'[NEW INVOICE
LIST.xlsb]INVOICE ''05'!$A$7:$AX$6000,29,FALSE)),VLOOKUP(H6674,'[NEW
INVOICE
LIST.xlsb]INVOICE ''05'!$A$7:$AX$6000,29,FALSE)),VLOOKUP(H6674,'[NEW
INVOICE
LIST.xlsb]INVOICE ''06'!$A$7:$AX$6000,31,FALSE)),VLOOKUP(H6674,'[NEW
INVOICE
LIST.xlsb]INVOICE ''07'!$A$7:$AX$6000,31,FALSE)),VLOOKUP(H6674,'[NEW
INVOICE
LIST.xlsb]INVOICE ''08'!$A$7:$AX$6000,32,FALSE))

--
H. Frank Situmorang



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 65
Default Blank date column return datel 00-Jan-00

Thanks Valko, there is another way to do it, but since I do not want to
insert column anymore, I leave it as it is.

Thanks you very much

Frank
--
H. Frank Situmorang


"T. Valko" wrote:

Use another cell!

Use that current formula and "hide" it somewhere. Then use another formula
that tests that cell for 0.

Your long formula in cell A1.

Then test that cell:

=IF(A1=0,"",A1)

--
Biff
Microsoft Excel MVP


"M Kan" <tipsoftheweek at gmail dot com wrote in message
...
You could add another IF statement that says if the result = 0, then
return
"" otherwise just execute the formula you just laid out.
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Frank Situmorang" wrote:

Hello:

Following is my formula to be put in data sheet ( destination sheet)
while
New Invoice List is the name of my source sheet.

My problem is the blank date in the source sheet is shown as " 0-Jan-00",
while I want it to renturn just as source/ blank.

Please help me how can we make it , because in the summary sheet I want
to
make the sum if blank or zero 0, will not be calculated.

=IF(ISNA(VLOOKUP(H6674,'[NEW INVOICE LIST.xlsb]INVOICE
''08'!$A$7:$AX$6000,32,FALSE)),IF(ISNA(VLOOKUP(H66 74,'[NEW INVOICE
LIST.xlsb]INVOICE
''07'!$A$7:$AX$6000,31,FALSE)),IF(ISNA(VLOOKUP(H66 74,'[NEW
INVOICE LIST.xlsb]INVOICE
''06'!$A$7:$AX$6000,31,FALSE)),IF(ISNA(VLOOKUP(H66 74,'[NEW INVOICE
LIST.xlsb]INVOICE ''05'!$A$7:$AX$6000,29,FALSE)),VLOOKUP(H6674,'[NEW
INVOICE
LIST.xlsb]INVOICE ''05'!$A$7:$AX$6000,29,FALSE)),VLOOKUP(H6674,'[NEW
INVOICE
LIST.xlsb]INVOICE ''06'!$A$7:$AX$6000,31,FALSE)),VLOOKUP(H6674,'[NEW
INVOICE
LIST.xlsb]INVOICE ''07'!$A$7:$AX$6000,31,FALSE)),VLOOKUP(H6674,'[NEW
INVOICE
LIST.xlsb]INVOICE ''08'!$A$7:$AX$6000,32,FALSE))

--
H. Frank Situmorang




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula to return position of the next Non-blank cell in a column PCLIVE Excel Worksheet Functions 14 July 17th 07 01:11 PM
Return next non-Blank Cell in a Column ? Jakobshavn Isbrae Excel Worksheet Functions 3 May 23rd 07 10:28 PM
Find First Non blank cell than find column header and return that value Silver Rose Excel Worksheet Functions 10 April 30th 07 05:56 PM
Lookup / return latest date in range AND value of 1 column to the wellan Excel Worksheet Functions 3 February 26th 07 05:26 PM
Insert A blank Row Everytime the date changes in column B Dtown Dawg Excel Discussion (Misc queries) 3 November 15th 06 06:08 PM


All times are GMT +1. The time now is 11:22 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"