[Solved] #DIV/0! on spreadsheet created in Excel

[MFerguson]

I have a spreadsheet that was created in Excel. I have a tab with input information. We will continue to add rows to this tab with monthly data. I created a summary tab using multiple formulas including offset, index, match and countA. The formulas work in Excel, I opened it in LibreOffice, but when I open it in OpenOffice, I get #DIV/0! in all cells. My summary tab needs to pull data from the most recent 12 months and I was hoping for it to automatically populate as the inputs change. Below are a couple of the formulas that result in #DIV/0!. It appears that the functions are available in Open Office so I am assuming maybe it is a difference in syntax between Excel and OpenOffice. Any help is appreciated.

=LOOKUP(2;1/($Invoices.$A$1:$A$1048576<>"");$Invoices.$A$1:$A$1048576)

=OFFSET(INDEX(OFFSET($Invoices.$A$4;0;0;COUNTA($Invoices.$A$1:$A$1048576)-1;1);MATCH(A4;OFFSET($Invoices.$A$4;0;0;COUNTA($Invoices.$A$1:$A$1048576)-1;1);0);1);-1;0)

[FJCC]

The LOOKUP() formula will throw ad #DIV/0 error whenever the comparison

Code: Select all

$Invoices.$A$1:$A$1048576<>"")

is FALSE because FALSE = 0 and you are dividing by that quantity. Excel has a "feature" that allowed this to slip through without an error and it turns out to be useful in LOOKUP() formulas. I guess LibreOffice has mimicked this bug. I can't remember what the point of it is. Please remind me of the purpose and maybe someone can come up with another way to do the same thing.
I don't see a problem with the second formula. Is it possible that one of the cells it depends on contains a #DIV/0?

[MFerguson]

We used the Lookup(2,1/ code to give the value in the last row of the input tab. 1/TRUE or 1/FALSE is going to result in 1 or #DIV/0!. It is supposed to find the last non-blank cell in column A (which has dates) and return that date.

Then the other cells lookup the date and fill in the table with corresponding columns information. If I manually put a date in, it does fill in the rest of the table with one exception. I get an Err:509 in a cell using this formula =HYPERLINK(VLOOKUP(A4;$Invoices.$A$4:A4($Invoices.$A$1:$H$1048576;A4;8);8;0);LOOKUP($A4;$Invoices.$A$1:$A$1048576;$Invoices.$E$1:$E$1048576))

All of my Excel formulas were $A:$A or similar and it looks like Open Office uses the 1048576 since it is the last row available.

[FJCC]

I should have asked earlier, are the data guaranteed to be contiguous or might there be blank cells along the way to the last used cell in column A?

[FJCC]

If there are no blank cells you can use

Code: Select all

=OFFSET(A1;COUNTA(A1:A1048576)-1 ;0)

to get the content of the last value. If there are blanks you can use the array formula

Code: Select all

{=OFFSET(A1;MAX(NOT(ISBLANK(A1:A1048576)) * ROW(A1:A1048576)) -1;0)}

[MFerguson]

I will give these a try. Thank you.