Formula won't work beyond a certain row

Question

Level 1

9 points

Formula won't work beyond a certain row

I've had this problem since creating the spreadsheet a couple years ago but haven't had time to work on it until now. I've checked, double checked, triple checked everything but can't seem to find where I am going wrong.

OS X 10.8.5

Numbers 09 Version 2.3 (554)

Weekly Standings Tables

The tables above contain the amounts won per tournament by each person. Based on the standings the first 2 names are populated into the sheet below.

Weekly Cash Winners Table

Everything works fine until I get to Week 17. At that point the Winner and 2nd Place names will not "auto-populate".

Am I missing something so obvious I cannot see it? Please let me know if you need more information and I will provide it.

Thanks - James

Numbers 09-OTHER

Posted on May 19, 2015 8:28 AM

Reply

Answer 1

jaxjason

Level 5

5,047 points

May 19, 2015 10:59 AM in response to jandscanada

The only thing I can tell from your screenshots and my testing, is that if Table17 and higher dont have anything in column B, that would bring back an empty string, which it looks like.

It appears you had to make every equation unique for every cell because all your data is on different tables.

You could add a column in your totals table with sheet name (you can hide it later), then just use Indirect with address to pull back the one and two spots using The exact row and column, with sheet name called out. One equation for each column. Much easier to maintatin later.

Jason

Reply

Answer 2

May 19, 2015 1:12 PM in response to jandscanada

Hello jandscanada.

I believe jaxjason is correct. If I understand what you are trying to accomplish with your formulas and tables, you want to know who came in first and second place of any tournament.

One reason for no answer for week 17 is that no one person has greater than a "0" in column "B". Your formula looks a value greater than "0". If no value is greater than "0", the formula returns a blank. Try keying in some values in column B of your week 17 RBC Canadian tournament.

I hope this helps. If not, I wouldn't mind having a deeper look at the spreadsheet, if you wouldn't mind sharing the file.

Regards,

SoftwareAddict1462

Reply

Answer 3

jandscanada Author

Level 1

9 points

May 19, 2015 2:41 PM in response to SoftwareAddict1462

jaxjason and SoftwareAddict1462. Thanks for the speedy reply to my question. I should have mentioned that I've put amounts into the sheets for various weeks after Week16 to "test" it and it still won't give a name. The Weekly Standings Column are populated from data in the sheet below.

- James

Reply

Answer 4

May 22, 2015 12:57 PM in response to jandscanada

Hello Jason

Not sure if you have resolved your issue yet. I created a spreadsheet loosely based on your examples, but could not recreate the problem.

Just an idea to help troubleshoot the issue. Try changing out the Table Name where the formula is not working. For example, try changing out Week 16 for Week 1. If the formula is working correctly, we would expect the answer from Week 1, which we know is working correctly. If no answer is provided, try rewriting the formula again.

Good Luck

SoftwareAddict1462

Reply

Answer 5

jandscanada Author

Level 1

9 points

May 26, 2015 7:12 AM in response to SoftwareAddict1462

Hi. I tried your suggestion but it still failed to fill in a name. I haven't had much time to work on this lately but will try to rewrite those formulas and let you know how it went.

Thanks - James

Reply