# I need help with Error 511 in Calc please

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

http://www.get-digital-help.com/2010/...

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

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:

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

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:

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.

Alex

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

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.

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

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)

Note that MS Excel opens it with this error:

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.