Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to return position of the next Non-blank cell in a column | Excel Worksheet Functions | |||
Return next non-Blank Cell in a Column ? | Excel Worksheet Functions | |||
Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
Lookup / return latest date in range AND value of 1 column to the | Excel Worksheet Functions | |||
Insert A blank Row Everytime the date changes in column B | Excel Discussion (Misc queries) |