OpenOffice v LibreOffice Calc Function problem

English
,
#1

Hi all

My Company used to use OpenOffice and I developed a Spreadsheet to calculate and print off a chart of colleague performances.
Now, with an upgrade of PC’s, they have switched to LibreOffice and I am having some issues with LOOKUP’s that have worked for in both OO Calc and MS Excel.

Specifically, I created a Pivot Table from the data and it has a filter to pick specific weeks, so as weekly data is added, I then selected the Week number which would populate another sheet from which I print an A4 sheet for the office notice board!

This sheet does a LOOKUP calc using the Filter week number.
Since LibreOffice has been used, this LOOKUP now fails

So if the Cell reference is on sheet Weekly B2 and the current value is 19 (this week), if I do =$Weekly.B2 in a cell, it returns correctly the value 19
If, however, I use =LOOKUP($Weekly.B2,Disp_Collation.C730:P781,Disp_Collation.K730:K781) I get a #N/A returned and at the bottom of the sheet, the error: “Error: Value not available”

Any tips on how to address this please
Thanks
NeilCalcs.ods

#2

Maybe is in relation with Regular expression/Wildcards, LibreOffice use by default the second on opening excel format files. Menu/Tools/Options/LibreOffica calc/Calculate.

#3

Not only for Excel: see https://wiki.documentfoundation.org/ReleaseNotes/5.3#Option_settings

#4

Thanks @Mike2, I had forgotten that change.

#5

Not sure I understand the answer, but I looked at the Options and tried all of them, none make a difference to the Calculation, still showing #N/A
The original ISN’T an Excel document, it was created entirely in OpenOffice Calc, which I always thought was a ‘brother’ of LibreOffice?

#6

Please provide a sample document. It would allow to tell the problem directly, instead of trying to guess.

#7

How do I provide the file?
Can I remove it later?

#8

OK, I added ‘calcs.ods’ to the original post by editing it.
The error is occurring on TLCalcs - it should open on that page.

#9
  1. In your Weekly.$B$2, the value is text, while in formulas in TLCalcs.B:B, it’s searched in column C of sheet Disp_Collation, where there are numbers. Replacing these formulas to have LOOKUP(VALUE(Weekly.$B$2);Disp_Collation.C730:P781;... makes them calculate correctly. Possibly that’s a bug (because the page field in pivot table on Weekly is taken from numeric values) - please file a bug report for that.
  2. The formula in TLCalcs.B2 refers to wrong range on Disp_Collation, so the range fails the constraint that it’s sorted ascending (the range should be from row 730 to row 781, but actually it’s from row 727 to row 778).

The two changes is enough to make formulas marked red to work OK.

By the way: I have checked your spreadsheet with AOO 4.1.5, and it indeed does not show #N/A there. It simply gives you wrong results, that’s all. Making the modifications as I wrote above also changes results. Being “brother” does not mean LO needs to have its brother’s bugs.

#10

Thanks Mike. I tried the Lookup(value) as you suggested and it looks good.
When I originally created the sheet in OO I obviously created the Pivot table and the filter. I hadn’t given any consideration to making the filter field (B2 in this case) as ‘number’. I’m more used to Excel and have never had to do anything like that in Excel as far as I remember. They just work!
The range of C730:P781 used to be correct in previous versions, I made a slip when modifying something else last week.

Another thing I am curious about, on my home PC the Sheets I have protected (to prevent the staff ‘fiddling’ when they input!) show a padlock, while at work they don’t. Is this a setting somewhere?
As far as I know, both have the latest version of LO installed with default settings, both on Windows 10 Pro

Not a problem, just curious.

#11

About padlock - please check the version at work.

#12

Yes, older version at work, now updated and see the padlock

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.