[Solved] Error code 509

[rallycan]

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

[FJCC]

I think you want

Code: Select all

==if('Entry List'.P2="n";"DNS";SUM(D2:I2))

[robleyd]

Hi, and welcome to the community forum.

Correct syntax for IF() is

Code: Select all

=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

 =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 =IF(UPPER('Entry List'.p2)="N";"DNS";SUM(d2:i2)) Then P2 could be either n or N and satisfy the test

[rallycan]

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

[robleyd]

Just to clarify - robleyd is David

The #NAME error may have been caused by single rather than double quotes - anyway you resolved your problem.