Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
This formula can not work in Excel 2007
Hello,
Since we used Office 2007, there are many complaint of instablility of this software. This is my problem, I have checked that the referenced cell does Exits for Date, but it says NA in the destatination worksheet. Here is my formula in the cell: =IF(ISNA(VLOOKUP(H6350, 'C:\hfsfile\Revenue\[NEW INVOICE LIST.xls]INVOICE ''08'!$A$7:$AX$6000,32,FALSE)),IF(ISNA(VLOOKUP(H63 50, 'C:\hfsfile\Revenue\[NEW INVOICE LIST.xls]INVOICE ''07'!$A$7:$AX$5018,31,FALSE)),IF(ISNA(VLOOKUP(H63 50, 'C:\hfsfile\Revenue\[NEW INVOICE LIST.xls]INVOICE ''06'!$A$7:$BB$5000,31,FALSE)),IF(ISNA(VLOOKUP(H63 50, 'C:\hfsfile\Revenue\[NEW INVOICE LIST.xls]INVOICE ''05'!$A$6:$AY$5000,29,FALSE)),VLOOKUP(H6350, 'C:\hfsfile\Revenue\[NEW INVOICE LIST.xls]INVOICE ''07'!$A$7:$BB$5018,31,FALSE),VLOOKUP(H6350, 'C:\hfsfile\Revenue\[NEW INVOICE LIST.xls]INVOICE ''05'!$A$6:$AZ$5000,29,FALSE)),VLOOKUP(H6350, 'C:\hfsfile\Revenue\[NEW INVOICE LIST.xls]INVOICE ''06'!$A$7:$BA$5000,31,FALSE)),VLOOKUP(H6350, 'C:\hfsfile\Revenue\[NEW INVOICE LIST.xls]INVOICE ''07'!$A$7:$AX$5018,31,FALSE)),VLOOKUP(H6350, 'C:\hfsfile\Revenue\[NEW INVOICE LIST.xls]INVOICE ''08'!$A$7:$AX$6000,32,FALSE)) Pls help us what is wrongf in the formula Thanks in addvance -- H. Frank Situmorang |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
This formula can not work in Excel 2007
to me:
1. INVOICE''08 name of the sheet looks strange 2. tried to rename the workbook to NEW_INVOICE_LIST.xls ? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
This formula can not work in Excel 2007
On Wed, 16 Jul 2008 01:56:29 -0700, Frank Situmorang
wrote: Hello, Since we used Office 2007, there are many complaint of instablility of this software. This is my problem, I have checked that the referenced cell does Exits for Date, but it says NA in the destatination worksheet. Here is my formula in the cell: =IF(ISNA(VLOOKUP(H6350, 'C:\hfsfile\Revenue\[NEW INVOICE LIST.xls]INVOICE ''08'!$A$7:$AX$6000,32,FALSE)),IF(ISNA(VLOOKUP(H6 350, 'C:\hfsfile\Revenue\[NEW INVOICE LIST.xls]INVOICE ''07'!$A$7:$AX$5018,31,FALSE)),IF(ISNA(VLOOKUP(H6 350, 'C:\hfsfile\Revenue\[NEW INVOICE LIST.xls]INVOICE ''06'!$A$7:$BB$5000,31,FALSE)),IF(ISNA(VLOOKUP(H6 350, 'C:\hfsfile\Revenue\[NEW INVOICE LIST.xls]INVOICE ''05'!$A$6:$AY$5000,29,FALSE)),VLOOKUP(H6350, 'C:\hfsfile\Revenue\[NEW INVOICE LIST.xls]INVOICE ''07'!$A$7:$BB$5018,31,FALSE),VLOOKUP(H6350, 'C:\hfsfile\Revenue\[NEW INVOICE LIST.xls]INVOICE ''05'!$A$6:$AZ$5000,29,FALSE)),VLOOKUP(H6350, 'C:\hfsfile\Revenue\[NEW INVOICE LIST.xls]INVOICE ''06'!$A$7:$BA$5000,31,FALSE)),VLOOKUP(H6350, 'C:\hfsfile\Revenue\[NEW INVOICE LIST.xls]INVOICE ''07'!$A$7:$AX$5018,31,FALSE)),VLOOKUP(H6350, 'C:\hfsfile\Revenue\[NEW INVOICE LIST.xls]INVOICE ''08'!$A$7:$AX$6000,32,FALSE)) Pls help us what is wrongf in the formula Thanks in addvance IF the referenced cell is properly referenced, my guess is that the contents of H6350 doesn't *exactly* match anything in the range defined for any of the tables. --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
This formula can not work in Excel 2007
Thanks to all of you, Ron and Jarek. In fact the problem is that the source
sheet and the destination sheet are different, one in binary and one in nomral excel. Thanks very much -- H. Frank Situmorang "Ron Rosenfeld" wrote: On Wed, 16 Jul 2008 01:56:29 -0700, Frank Situmorang wrote: Hello, Since we used Office 2007, there are many complaint of instablility of this software. This is my problem, I have checked that the referenced cell does Exits for Date, but it says NA in the destatination worksheet. Here is my formula in the cell: =IF(ISNA(VLOOKUP(H6350, 'C:\hfsfile\Revenue\[NEW INVOICE LIST.xls]INVOICE ''08'!$A$7:$AX$6000,32,FALSE)),IF(ISNA(VLOOKUP(H6 350, 'C:\hfsfile\Revenue\[NEW INVOICE LIST.xls]INVOICE ''07'!$A$7:$AX$5018,31,FALSE)),IF(ISNA(VLOOKUP(H6 350, 'C:\hfsfile\Revenue\[NEW INVOICE LIST.xls]INVOICE ''06'!$A$7:$BB$5000,31,FALSE)),IF(ISNA(VLOOKUP(H6 350, 'C:\hfsfile\Revenue\[NEW INVOICE LIST.xls]INVOICE ''05'!$A$6:$AY$5000,29,FALSE)),VLOOKUP(H6350, 'C:\hfsfile\Revenue\[NEW INVOICE LIST.xls]INVOICE ''07'!$A$7:$BB$5018,31,FALSE),VLOOKUP(H6350, 'C:\hfsfile\Revenue\[NEW INVOICE LIST.xls]INVOICE ''05'!$A$6:$AZ$5000,29,FALSE)),VLOOKUP(H6350, 'C:\hfsfile\Revenue\[NEW INVOICE LIST.xls]INVOICE ''06'!$A$7:$BA$5000,31,FALSE)),VLOOKUP(H6350, 'C:\hfsfile\Revenue\[NEW INVOICE LIST.xls]INVOICE ''07'!$A$7:$AX$5018,31,FALSE)),VLOOKUP(H6350, 'C:\hfsfile\Revenue\[NEW INVOICE LIST.xls]INVOICE ''08'!$A$7:$AX$6000,32,FALSE)) Pls help us what is wrongf in the formula Thanks in addvance IF the referenced cell is properly referenced, my guess is that the contents of H6350 doesn't *exactly* match anything in the range defined for any of the tables. --ron |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
This formula can not work in Excel 2007
On Thu, 17 Jul 2008 19:04:01 -0700, Frank Situmorang
wrote: Thanks to all of you, Ron and Jarek. In fact the problem is that the source sheet and the destination sheet are different, one in binary and one in nomral excel. Thanks very much -- H. Frank Situmorang Do you mean that one is an xlsb file and the other not? Or is the binary file not an Excel file at all? If the "binary" file is an xlsb file, I would not have expected that to make a difference. Can you provide a little more information? I'd like to try to reproduce that issue here. --ron |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
This formula can not work in Excel 2007
Yes Ron...the destination workbook is xlsb ang the source workbook is xls.
Can it still be working even though different type? The other one Ron, my problem now in this vlookup formula in the source date column it the date is nothing/blank, but in the destination workbook it shows " 0-Jan-00". How can I make it to be the same...(looked blank), the date "0-Jan-00" doesnot mean anything, and I am affraid if I make the formula sum if zerro,......it will effect the result) Thanks for your help. -- H. Frank Situmorang "Ron Rosenfeld" wrote: On Thu, 17 Jul 2008 19:04:01 -0700, Frank Situmorang wrote: Thanks to all of you, Ron and Jarek. In fact the problem is that the source sheet and the destination sheet are different, one in binary and one in nomral excel. Thanks very much -- H. Frank Situmorang Do you mean that one is an xlsb file and the other not? Or is the binary file not an Excel file at all? If the "binary" file is an xlsb file, I would not have expected that to make a difference. Can you provide a little more information? I'd like to try to reproduce that issue here. --ron |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
This formula can not work in Excel 2007
On Thu, 17 Jul 2008 20:48:02 -0700, Frank Situmorang
wrote: Yes Ron...the destination workbook is xlsb ang the source workbook is xls. Can it still be working even though different type? The other one Ron, my problem now in this vlookup formula in the source date column it the date is nothing/blank, but in the destination workbook it shows " 0-Jan-00". How can I make it to be the same...(looked blank), the date "0-Jan-00" doesnot mean anything, and I am affraid if I make the formula sum if zerro,......it will effect the result) Thanks for your help. -- H. Frank Situmorang Sorry for the delay in responding, Frank. For some reason, this message of yours did not appear on my usual computer. I'm on a different computer (in a different location) and today is the first I've seen it. In any event, I would have thought that if a function can work on a closed workbook, it shouldn't matter if it was a different "type" of Excel workbook. But I'll have to do some experimentation. So far as your VLOOKUP returning a 0 when the original is blank: I'm not sure I understand what you are trying to do, or not do, and how it relates to your comment "make the formula sum if zerro" --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get Bullen's FilterCriteria() to work in Excel 2007? | Excel Discussion (Misc queries) | |||
EXCEL 97 WORK IN 2007? | Excel Discussion (Misc queries) | |||
How do I work with Excel 2007 and htm files? | New Users to Excel | |||
Datedif doesn't work the same in Excel 2007 | Excel Worksheet Functions | |||
Lost all my work in Excel 2007 | Excel Discussion (Misc queries) |