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

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 ?

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

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

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

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

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