[Solved] Error code 509

Discuss the spreadsheet application

[Solved] Error code 509

Postby rallycan » Mon Jun 19, 2017 4:58 am

I am getting a 509 error code for the following statement =if'Entry List.p2'=n;"DNS;sum(d2:i2)

This is entered into a cell in a tab called ERF in a document called Scores.

"Entry List.P2 is a cell in a another tab in the same document called Entry List . It will have either a n (no) or a y (y) in that cell.

The formula sum(d2:j2) simply adds the value in cells D2:J2. That formula works fine on it's own

The intent is to identify whether someone is entered into an event called EFR. If the answer is n, then that person is shown as DNS. If the answer is not an n, then their scores in cells d2:J2 are totalled.

The name of the individual, also in the Entry List tab, has transferred without problems to it's appropriate cell in the EFR tab.



Can someone show more how to correct the syntax to make this work
Last edited by rallycan on Tue Jun 20, 2017 11:33 pm, edited 2 times in total.
Open Office 4 Windows 7
rallycan
 
Posts: 2
Joined: Sun Jun 18, 2017 3:56 pm

Re: Error code 509

Postby FJCC » Mon Jun 19, 2017 5:15 am

I think you want
Code: Select all   Expand viewCollapse view
==if('Entry List'.P2="n";"DNS";SUM(D2:I2))
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 6033
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Error code 509

Postby robleyd » Mon Jun 19, 2017 5:17 am

Hi, and welcome to the community forum.

Correct syntax for IF() is
Code: Select all   Expand viewCollapse view
=IF(Test; ThenValue; OtherwiseValue)

You are missing the outer brackets around the arguments to IF, quotes around the value for Test and missing a closing quote on the ThenValue; so your formula should look like
Code: Select all   Expand viewCollapse view
=IF('Entry List'.p2="n";"DNS";SUM(d2:i2))


I'm not sure but you may need double quotes around Entry List - personally I try and avoid spaces in sheet names :)

 Edit: To avoid the Test being case sensitive, you could add UPPER() to the formula:

Code: Select all   Expand viewCollapse view
=IF(UPPER('Entry List'.p2)="N";"DNS";SUM(d2:i2))

Then P2 could be either n or N and satisfy the test 
Cheers
David
OpenOffice 4.1.3 - Windows 7
OpenOffice 4.2.0 - Slackware 14.2 - 64 bit
User avatar
robleyd
Volunteer
 
Posts: 951
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Error code 509

Postby rallycan » Mon Jun 19, 2017 4:07 pm

Thank you Robleyd and David - it took both suggestions to get it to work. Using Robleyd's format (which looks perfectly good to me), I ended up with #NAME errors. Then I took David's suggestion and changed the Entry List tab to Entry - one word - and it now seems to have word perfectly. Might be a quirk in my laptop or version of OO. Thx to both of you
Open Office 4 Windows 7
rallycan
 
Posts: 2
Joined: Sun Jun 18, 2017 3:56 pm

Re: [Solved] Error code 509

Postby robleyd » Tue Jun 20, 2017 12:59 am

Just to clarify - robleyd is David :)

The #NAME error may have been caused by single rather than double quotes - anyway you resolved your problem.
Cheers
David
OpenOffice 4.1.3 - Windows 7
OpenOffice 4.2.0 - Slackware 14.2 - 64 bit
User avatar
robleyd
Volunteer
 
Posts: 951
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia


Return to Calc

Who is online

Users browsing this forum: Google [Bot], Yahoo [Bot] and 19 guests