Top 15 on another sheet

[LeeBillington]

Hey guys,

Quick question.

How would i go about getting a top 15 numbers from sheet 'pickrate' back to 'sheet1' for example

So the numbers are in sheet 'Pickrates' between k6:k1000

but also how could i also get the name allocated to them top numbers in a different column? the names are in B6:B1000

both returning back to 'sheet1' from pickrate.

 

[Eric W]

Try:

=IFERROR(IF(D7="","",VLOOKUP(D7,Register!$B$6:$D$401,2,FALSE)),"-")

In short, to use IFERROR, just put the formula that might generate an error inside IFERROR like this:

=IFERROR( ...my formula... , "what I want to see if there is an error")

 

[LeeBillington]

Thanks it worked and i learnt something!

 

[LeeBillington]

So sad news.. i can't use index on the the computer at my work place. there's is 1997-2003 .. so outdated i know! haha is there anyway round that?

 

[MARK858]

Possibly (untested)...

=IF(D7="","",IF(ISERROR(VLOOKUP(D7,Register!$B$6:$D$401,2,FALSE)),"-",VLOOKUP(D7,Register!$B$6:$D$401,2,FALSE)))

or

=IF(D7="","",IF(ISNA(VLOOKUP(D7,Register!$B$6:$D$401,2,FALSE)),"-",VLOOKUP(D7,Register!$B$6:$D$401,2,FALSE)))

 

[LeeBillington]

{=INDEX(pickrate!$B$6:$B$1000,SMALL(IF(pickrate!$K$6:$K$1000=A2,ROW($K$6:$K$1000)-ROW($K$6)+1),COUNTIF($A$2:$A2,A2)))}

<tbody>
</tbody>

I mean't this one isn't working,

{=INDEX(pickrate!$B$6:$B$1000,SMALL(IF(pickrate!$K$6:$K$1000=A2,ROW($K$6:$K$1000)-ROW($K$6)+1),COUNTIF($A$2:$A2,A2)))}

<tbody>
</tbody>

 

[LeeBillington]

I basically need what that formula says above me but not using index, as it doesn't work on 1997-2003 excel!?

 

[MARK858]

What makes you think that is the issue? INDEX has been in place since at least Excel 2000.
Are you sure you are using Ctrl+Shift Enter and not putting in the curly brackets manually?

Post your data.

 

[LeeBillington]

Oh really? it was just coming back as #NAME! when i used the exact same file at work? on the older excel.

 

[MARK858]

Just bumping this thread up as the OP has an issue where the formula is working in later versions of Excel but is producing a #NAME error in 97-2003.

The trouble is I can't see any function I remember as being non-compliant in 97-2003 and I haven't an old version to test on (plus my memory probably isn't being that good).

It would be appreciated if someone else could run their eyes over it.

The formula in question is

{=INDEX(pickrate!$B$6:$B$1000,SMALL(IF(pickrate!$K$6:$K$1000=A2,ROW($K$6:$K$1000)-ROW($K$6)+1),COUNTIF($A$2:$A2,A2)))}

 

[Eric W]

I used the formula from post #15 in a copy of Excel 2000 I have, and it worked fine.

One thing I noticed is that you said you used the same file at work. If your version of Excel at home is 2007 or newer, then the file format changed. On your home version, select Save As, and in the Save as type dropdown list, select Excel 97-2003. Then try loading that file into your work PC.