I need help with Error 511 in Calc please

[alexlmlo]

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/02/11/match-two-criteria-and-return-multiple-rows-in-excel/

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:

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

[mikekaganski]

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

[alexlmlo]

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]

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

[mikekaganski]

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)

[mikekaganski]

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.

[librebel]

Hello @alexlmlo,

As already explained above by @mikekaganski, 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.

[alexlmlo]

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]

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!

[librebel]

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)));"")

[mikekaganski]

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

[alexlmlo]

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

Alex