INDEX(LARGE( across worksheets
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!
|