Ask Your Question

OpenOffice v LibreOffice Calc Function problem

asked 2019-04-12 22:46:24 +0100

Neil Grantham gravatar image

updated 2019-04-13 00:18:24 +0100

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 NeilC:\fakepath\Calcs.ods

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2019-04-12 23:08:24 +0100

Neil Grantham gravatar image

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?

edit flag offensive delete link more


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

Mike Kaganski gravatar imageMike Kaganski ( 2019-04-12 23:22:24 +0100 )edit

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

Neil Grantham gravatar imageNeil Grantham ( 2019-04-13 00:07:14 +0100 )edit

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

Neil Grantham gravatar imageNeil Grantham ( 2019-04-13 00:20:12 +0100 )edit
  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 ...(more)

Mike Kaganski gravatar imageMike Kaganski ( 2019-04-13 08:24:21 +0100 )edit

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 ...(more)

Neil Grantham gravatar imageNeil Grantham ( 2019-04-13 11:17:12 +0100 )edit

About padlock - please check the version at work.

Mike Kaganski gravatar imageMike Kaganski ( 2019-04-13 11:27:36 +0100 )edit

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

Neil Grantham gravatar imageNeil Grantham ( 2019-04-15 12:51:39 +0100 )edit

answered 2019-04-12 22:55:30 +0100

m.a.riosv gravatar image

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.

edit flag offensive delete link more


Mike Kaganski gravatar imageMike Kaganski ( 2019-04-12 23:21:38 +0100 )edit

Thanks @Mike, I had forgotten that change.

m.a.riosv gravatar imagem.a.riosv ( 2019-04-14 00:55:43 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-04-12 22:46:24 +0100

Seen: 91 times

Last updated: Apr 13 '19