Ask Your Question

I need help with Error 511 in Calc please [closed]

asked 2018-01-09 01:07:52 +0200

alexlmlo gravatar image

updated 2020-08-03 12:47:00 +0200

Alex Kemp gravatar image

Hi, I am building a formula to match two criteria and have multiple results, based on the tutorial found from here:

The file link is here (sorry I don’t have enough points to upload file yet)

link text

I am building a spreadsheet in use for our pharmacy, in which one sheet (Patients list) we put the patient names and details, such as the following:

image description

Then on another sheet "Week 1", the patient name will appear under the desired location accordingly as follow:

image description

I used google sheets to create this on the go before I download it onto the pharmacy computer, which they use LibreOffice. The formula under "Week 1" sheet, cell B4, is:

=Arrayformula(iferror(index('Patients list'!$A$2:$AA$100,(small(if(COUNTIF($B$1,'Patients list'!$C$2:$C$50)*COUNTIF($A$3,'Patients list'!$D$2:$D$50),row($A$2:$D$58)-min(row($A$2:$D$58))+1),ROW(A1))),COLUMN(A1))))

Which work fine on Google sheets. However once I downloaded the file as OpenOffice ods file, it became like this:

image description

The code under cell B4 is now:

{=IFERROR(INDEX($'Patients list'.$A$2:$AA$100,(SMALL(IF(COUNTIF($B$1,$'Patients list'.$C$2:$C$50)*COUNTIF($A$3,$'Patients list'.$D$2:$D$50),ROW($A$2:$D$58)-MIN(ROW($A$2:$D$58))+1),ROW(A1))),COLUMN(A1)))}

Which is the same as before, but it all have the error code 511. I found on the open office site, the error code 511 is:

511 Missing variable Function requires more variables than are provided, for example, AND() and OR().

I really don't know what was missing, since the formula was working perfectly on google sheets, that's why I come to here and post this question, and hope some genius can help me out.

Thank you very much spending your time to read this question, I hope to have an answer from one of you soon.


edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-07-10 21:00:00.939347


Please provide a sample file. Or do you expect others to try to parse the formula without data?

Mike Kaganski gravatar imageMike Kaganski ( 2018-01-09 08:12:37 +0200 )edit

Hi, sorry I’m very new here, sorry for the inconvenience. I have tried to upload the file but the system said I don’t have enough points to do it, I have put the spreadsheet link from my google drive on the post instead, hope it would help.

alexlmlo gravatar imagealexlmlo ( 2018-01-09 15:04:19 +0200 )edit

Just found a site which can hold file for 30 days, I have uploaded the file and edited the post, hope this help.

alexlmlo gravatar imagealexlmlo ( 2018-01-09 15:11:31 +0200 )edit

Look at this answer, and use the Function Wizard on parameters.K1 to see that COUNTIF(TODAY();$B$1:$H$1000) is the fist to have the error, because it has its parameters in wrong order.

Also the ISERROR function expects the second argument (what to do when the first argument is indeed the error)

Mike Kaganski gravatar imageMike Kaganski ( 2018-01-09 15:13:29 +0200 )edit

Note that MS Excel opens it with this error:

We found a problem with some content in testing-trays.xlsx. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.

When tried to recover, the formulas in most cells are gone, and cached values are in those cell. So, the workbook is exported wrong by GSheets.

Mike Kaganski gravatar imageMike Kaganski ( 2018-01-09 15:28:37 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2018-01-09 15:47:34 +0200

librebel gravatar image

updated 2018-01-09 17:07:49 +0200

Hello @alexlmlo,

As already explained above by @Mike Kaganski, the second parameter of the IFERROR() function is required. You could just pass "" ( or 0 ) as the second parameter, then your function should be OK.

edit flag offensive delete link more


Hi, thanks for the help from all the great people here, I have added the second parameter as follow:

=Arrayformula(iferror(index('Patients list'!$A$2:$AA$100,(small(if(COUNTIF($B$1,'Patients list'!$C$2:$C$50)*COUNTIF($A$3,'Patients list'!$D$2:$D$50),row($A$2:$D$58)-min(row($A$2:$D$58))+1),ROW(A1))),COLUMN(A1)),""))

and now it work fine, although some cell became "0" if the original cell was empty.

alexlmlo gravatar imagealexlmlo ( 2018-01-10 00:29:39 +0200 )edit

I have attached the ods file link here:

link text

If anyone can help me to eliminate these 0 would be great!

You can find those 0s on Week 1 sheet, cell J4 onwards.

Thanks for everyone help so far!

alexlmlo gravatar imagealexlmlo ( 2018-01-10 00:31:30 +0200 )edit

No idea if the result is semantically correct, but the 0 can be eliminated using the T() function around the INDEX() function e.g.

=IFERROR(T(INDEX($'Patients list'.$A$2:$AA$100;(SMALL(IF(COUNTIF($B$1;$'Patients list'.$C$2:$C$50)*COUNTIF($A$3;$'Patients list'.$D$2:$D$50);ROW($A$2:$D$58)-MIN(ROW($A$2:$D$58))+1);ROW(I1)));COLUMN(I1)));"")

librebel gravatar imagelibrebel ( 2018-01-10 00:48:14 +0200 )edit

Or you could simply use user format code like 0;-0;

Mike Kaganski gravatar imageMike Kaganski ( 2018-01-10 01:49:21 +0200 )edit

Thanks for the input, all the 0s are gone now, thanks for the help from all of you guys.


alexlmlo gravatar imagealexlmlo ( 2018-01-10 23:23:46 +0200 )edit

Question Tools

1 follower


Asked: 2018-01-09 01:07:52 +0200

Seen: 6,253 times

Last updated: Jan 09 '18