On Feb 13, 11:56 am, Domenic wrote:
Use the same define names provided by Harlan, and add the following
defined names...

Col_A_N:

=N(INDIRECT("'"&XWSLST&"'!A"&(2+MOD(S,N))))

Col_A_T:

=T(INDIRECT("'"&XWSLST&"'!A"&(2+MOD(S,N))))

Then try the following formula...

=INDEX(Col_A_N,MATCH(LARGE(IF(RIGHT(Col_A_T,5)="To tal",IF(LEFT(Col_A_T,5)
<"Grand",Col_B-S/10^5)),ROWS(D$2:D2)),Col_B-S/10^5,0)-1)

Note that I've assumed the value to return is a number, as per your
sample data in a previous thread.

Hope this helps!

In article .com,
"Fin Fang Foom" wrote:



I would like to get this formula below to work across worksheets.

=INDEX(A$1:A$99,MATCH(LARGE(IF(RIGHT($A$2:$A$100,5 )="Total",IF(LEFT($A
$2:$A$100,5)<"Grand",$B$2:$B$100-ROW($B$2:$B$100)/10^5)),ROWS(D
$2:D2)),$B$2:$B$100-ROW($B$2:$B$100)/10^5,0))

The formula above list names from column A that has the largest number
in column B. It also accounts for duplicates.

Any Ideas will be appreciated.- Hide quoted text -

- Show quoted text -


Hi Domenic, ThankYou! very much.

Thank You for repyling. I was really closes on my end I was still
using the ROW Function but in your formula you didn't, that was the
key. Also I had to take out the -1 in the formula to get the correct
results. The value I'm returning is text.

Another question, I dont know this is possible.

When using the formula it returns the text value that has the highest
qty. But if the text value say part# 20-T it will come out maybe 3
times because that part number is in multiple worksheets. Is there a
way that the formula do a SUMIF(LARGE( and return the text value just
once?

Let me know I made it clear.