Ask Your Question
0

I need help with Error 511 in Calc please

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

alexlmlo gravatar image

updated 2018-01-09 15:10:35 +0200

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)

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

edit retag flag offensive close merge delete

Comments

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
1

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
0

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

Comments

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.

Alex

alexlmlo gravatar imagealexlmlo ( 2018-01-10 23:23:46 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 3,048 times

Last updated: Jan 09 '18