This formula can not work in Excel 2007

[Frank Situmorang]

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(H6350,
'C:\hfsfile\Revenue\[NEW INVOICE LIST.xls]INVOICE
''07'!$A$7:$AX$5018,31,FALSE)),IF(ISNA(VLOOKUP(H6350,
'C:\hfsfile\Revenue\[NEW INVOICE LIST.xls]INVOICE
''06'!$A$7:$BB$5000,31,FALSE)),IF(ISNA(VLOOKUP(H6350,
'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

 

[Jarek Kujawa]

to me:

1. INVOICE''08 name of the sheet looks strange
2. tried to rename the workbook to NEW_INVOICE_LIST.xls ?

 

[Ron Rosenfeld]

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(H6350,
'C:\hfsfile\Revenue\[NEW INVOICE LIST.xls]INVOICE
''07'!$A$7:$AX$5018,31,FALSE)),IF(ISNA(VLOOKUP(H6350,
'C:\hfsfile\Revenue\[NEW INVOICE LIST.xls]INVOICE
''06'!$A$7:$BB$5000,31,FALSE)),IF(ISNA(VLOOKUP(H6350,
'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

 

[Frank Situmorang]

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

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(H6350,
'C:\hfsfile\Revenue\[NEW INVOICE LIST.xls]INVOICE
''07'!$A$7:$AX$5018,31,FALSE)),IF(ISNA(VLOOKUP(H6350,
'C:\hfsfile\Revenue\[NEW INVOICE LIST.xls]INVOICE
''06'!$A$7:$BB$5000,31,FALSE)),IF(ISNA(VLOOKUP(H6350,
'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

 

[Ron Rosenfeld]

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

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

 

[Frank Situmorang]

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.

 

[Ron Rosenfeld]

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.

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