Excel Macro #N/A

[Sam Clough]

I am doing a Ranking schedule with 2500 ROWS plus the following Columns

Book 1 Actual Data which adds columns for various events. This works well

Book 2 Male Ranking

Book 3 Female Ranking

Book 4 Summary of all

I have buttons for

Male Ranking by Name
Male Ranking by Points
Male Ranking by State

Female Ranking by Name
Female Ranking by Points
Female Ranking by State

Overall Ranking by Name
Overall Ranking by Points
Overall Ranking by States
ale ranking by Name
Male Ranking by Points
Male Ranking by State
When I try to copy and paste Ranking Formula of =IF(D3="","",RANK(D3,$D$3:$D$300,0)) It inserts the apparent correct formula but comes up with #N/A on many of the Rows.

Can't seem to fix.

Anyone help?

 

[Sam Clough]

Yes - all FALSE

 

[Sam Clough]

FIRST SURNAME CLUB PTS RANKING

Above is Rows A B C D E

<tbody>
</tbody>

 

[MARK858]

Actually I might be miss-understanding your post #7.
Can you post the actual formula that is giving the #N/A (not the one you are copying from but the actual formula giving the #N/A).

What you have posted in post #12 is your column headings, Yes?
I don't want to know your headings I want know what is actually in your cells that you are referencing.

I think the best thing you can do is upload your file to a free file hosting site like Box.com and post a link in the thread (remember to mark it for sharing).

 

[Sam Clough]

Column D is =IF(Data2013!E7="","",Data2013!E7)

Column F is =IF(E3=0,"",RANK(E3,$E$3:$E$1000,0))

 

[Sam Clough]

OK.
2013 BA Rankings System Final.xlsm (file://SAMCLOUGH-PC/Users/SAM%20CLOUGH/Dropbox/2013%20BA%20Ranking%20System%20(Pilot)/2013%20BA%20Rankings%20System%20Final.xlsm)

 

[MARK858]

The way you posted the link isn't a valid link.

Is the formula below the formula actually producing the #N/A?

=IF(E3=0,"",RANK(E3,$E$3:$E$1000,0))

If it is then the ISNUMBER formula needs to be =ISNUMBER(E3) dragged down and if it produces FALSE then try applying the Text to Columns on Column E (as long as what you have in the cells appears to be a number).

 

[Sam Clough]

Yes

 

[MARK858]

What do you mean by "Yes", have you done the Text to Columns

 

[Sam Clough]

Can you see this link?

https://www.dropbox.com/s/6zeewutps3ayriz/2013 BA Rankings System Final.xlsm

 

[MARK858]

Your formula does not go high enough!!!! you only have it to row 1000, you have 2136 rows so it needs to be at least....
=IF(E3=0,"",RANK(E3,$E$3:$E$2136,0))