|
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 04:07 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com