Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

Help with Error 511 in libreOffice

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/

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.

Alex

Help with Error 511 in libreOffice

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/

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.

Alex

Help I need help with Error 511 in libreOfficeCalc 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/02/11/match-two-criteria-and-return-multiple-rows-in-excel/

The file link from my google drive 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.

Alex

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

The file link from my google drive 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.

Alex