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.

On Feb 13, 6:20 am, "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.

Bump!

The method, functionality and syntax is correct.
Use Tools Formula Auditing Evaluate Formula
to find the simple error.

On Feb 13, 10:18 am, "Herbert Seidenberg"
wrote:
The method, functionality and syntax is correct.
Use Tools Formula Auditing Evaluate Formula
to find the simple error.



HI Herbert Seidenberg,


I don't think nonthing is wrong with the formula. I would like to do
is to have this formula to look through every worksheet in the
workbook.

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.

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.

Try...

=INDEX(Col_A,MATCH(LARGE(IF(RIGHT(Col_A,5)="Total" ,IF(LEFT(Col_A,5)<"Gra
nd",IF(MATCH(Col_A,Col_A,0)=S+1,Col_B-S/10^5))),ROWS(D$2:D2)),Col_B-S/10^
5,0))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

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

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.

On Feb 13, 3:34 pm, Domenic wrote:
Try...

=INDEX(Col_A,MATCH(LARGE(IF(RIGHT(Col_A,5)="Total" ,IF(LEFT(Col_A,5)<"Gra
nd",IF(MATCH(Col_A,Col_A,0)=S+1,Col_B-S/10^5))),ROWS(D$2:D2)),Col_B-S/10^
5,0))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

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

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.


Hi Domenic sorry for the confusion.

The formula I'm after is to look through all the worksheets find all
the matchings in column A, then sums up all the matchings and the
formula should retrieve the highest matching first then second and so
on. Here is a formula below that works on one worksheet. But trying to
have it work on multiple worksheets might not be possible what do you
think?


=INDEX($A$2:$A$400,LARGE(IF($A$1:$A$399<$A$2:$A$4 00,IF(RIGHT($A$2:$A
$400,5)<"Total",
IF(LEFT($A$2:$A$400,5)<"Grand",SUMIF($A$2:$A$400, $A$2:$A$400,$B$2:$B
$400)))),ROWS(D$2:D2)))

On Feb 13, 4:12 pm, "Fin Fang Foom" wrote:
On Feb 13, 3:34 pm, Domenic wrote:





Try...

=INDEX(Col_A,MATCH(LARGE(IF(RIGHT(Col_A,5)="Total" ,IF(LEFT(Col_A,5)<"Gra
nd",IF(MATCH(Col_A,Col_A,0)=S+1,Col_B-S/10^5))),ROWS(D$2:D2)),Col_B-S/10^
5,0))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

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

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.

Hi Domenic sorry for the confusion.

The formula I'm after is to look through all the worksheets find all
the matchings in column A, then sums up all the matchings and the
formula should retrieve the highest matching first then second and so
on. Here is a formula below that works on one worksheet. But trying to
have it work on multiple worksheets might not be possible what do you
think?

=INDEX($A$2:$A$400,LARGE(IF($A$1:$A$399<$A$2:$A$4 00,IF(RIGHT($A$2:$A
$400,5)<"Total",
IF(LEFT($A$2:$A$400,5)<"Grand",SUMIF($A$2:$A$400, $A$2:$A$400,$B$2:$B
$400)))),ROWS(D$2:D2)))- Hide quoted text -

- Show quoted text -

Bump!

Try the following formulas, which need to be confirmed with
CONTROL+SHIFT+ENTER...

D2, copied down:

=LARGE(IF(RIGHT(Col_A,5)="Total",IF(LEFT(Col_A,5)< "Grand",IF(MATCH(Col_A
,Col_A,0)=S+1,Col_B))),ROWS($D$2:D2))

E2, copied down:

=INDEX(Col_A,SMALL(IF(RIGHT(Col_A,5)="Total",IF(LE FT(Col_A,5)<"Grand",IF
(MATCH(Col_A,Col_A,0)=S+1,IF(Col_B=D2,S+1)))),COUN TIF($D$2:D2,D2)))

Hope this helps!

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

Hi Domenic sorry for the confusion.

The formula I'm after is to look through all the worksheets find all
the matchings in column A, then sums up all the matchings and the
formula should retrieve the highest matching first then second and so
on. Here is a formula below that works on one worksheet. But trying to
have it work on multiple worksheets might not be possible what do you
think?


=INDEX($A$2:$A$400,LARGE(IF($A$1:$A$399<$A$2:$A$4 00,IF(RIGHT($A$2:$A
$400,5)<"Total",
IF(LEFT($A$2:$A$400,5)<"Grand",SUMIF($A$2:$A$400, $A$2:$A$400,$B$2:$B
$400)))),ROWS(D$2:D2)))

On Feb 14, 7:11 am, Domenic wrote:
Try the following formulas, which need to be confirmed with
CONTROL+SHIFT+ENTER...

D2, copied down:

=LARGE(IF(RIGHT(Col_A,5)="Total",IF(LEFT(Col_A,5)< "Grand",IF(MATCH(Col_A
,Col_A,0)=S+1,Col_B))),ROWS($D$2:D2))

E2, copied down:

=INDEX(Col_A,SMALL(IF(RIGHT(Col_A,5)="Total",IF(LE FT(Col_A,5)<"Grand",IF
(MATCH(Col_A,Col_A,0)=S+1,IF(Col_B=D2,S+1)))),COUN TIF($D$2:D2,D2)))

Hope this helps!

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

Hi Domenic sorry for the confusion.

The formula I'm after is to look through all the worksheets find all
the matchings in column A, then sums up all the matchings and the
formula should retrieve the highest matching first then second and so
on. Here is a formula below that works on one worksheet. But trying to
have it work on multiple worksheets might not be possible what do you
think?

=INDEX($A$2:$A$400,LARGE(IF($A$1:$A$399<$A$2:$A$4 00,IF(RIGHT($A$2:$A
$400,5)<"Total",
IF(LEFT($A$2:$A$400,5)<"Grand",SUMIF($A$2:$A$400, $A$2:$A$400,$B$2:$B
$400)))),ROWS(D$2:D2)))



Thank You for giving this formula this help greatly.


=LARGE(IF(MATCH(Col_A,Col_A,0)=S+1,MMULT((Col_A=TR ANSPOSE(Col_A))
+0,Col_B)),ROWS($I$2:I2))

Thank You Domenic!