Help with #VALUE error

[Norris92]

Hello-

I've created a sales spreadsheet and built two top ten sections, one for monthly leaders and one for yearly leaders. Within the two lists I have a column for rep name and a column for units sold. To display units sold in ranking order I used the Large function. Works fine. To display the reps name I used the following formula (I did not create it, I found it online):

=IF(ISNUMBER(U7),INDEX($D$19:$D$41,SMALL(IF($X$19:$X$41=U7,ROW($D$19:$D$41)-ROW($D$19)+1),COUNTIF(U$7:U7,U7))),"")

It worked great when I first used it but now when I try to change the column reference for unit sales number (from U to X) it returns a #VALUE error. The D column is referencing sales rep names. I don't understand where I went wrong. I realize that the reps names are in text but as I said it worked great the first time I built it. Any help is appreciated.

[Tony Valko]

Hard to say since the formula has references to both coulmn U and X in multiple locations.

Can you post a SMALL sample file with just the relevant data?

[Norris92]

Thanks for the reply. I'm embarrassed to ask but what's the securest way to upload a small sample? I'm not a guru by any sense, so some remedial help is appreciated.

[Norris92]

Here's a sample copy of the workbook. Sample Workbook.xlsx

[Tony Valko]

875kb

Eh, that's too big for me.

To me, a small file is about 20 rows worth of data.

[FDibbins]

aaww c'mon Tony, its only A1:Z47 lol

OK, 1st thing you need to do is remove the merge-cells formatting (and avoid that everywhere if at all possible, it just messes things up - like here). You can use format/center across selection instead.

2nd, that is an array formula...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[Norris92]

Thank You! I completely forgot about that step. I appreciate the help.

[Tony Valko]

aaww c'mon Tony...

I've been meaning to post an example of a top/bottom N list in the Tips and Tutorials forum.

Maybe I'll do that this weekend if things are slow.

[FDibbins]

Also, in F7, copied down, try this instead, it will eliminate the need to adjust the formula for months (PROVIDED the month name in E6 is identical to the month names in row 18)...
=LARGE(OFFSET($G$18,1,MATCH(LEFT($E$6,FIND(" ",$E$6,1)-1),$G$18:$Y$18,0)-1,25,1),ROW(A1))

[Norris92]

FDibbins, you solved my next issue with that formula. Forward thinking, I love it. Thanks again, to you both for your help. Sorry about the file size, apparently size does matter. Have a great weekend.

[FDibbins]

Happy to help