G
Gustavo
Hi Everyone,
I have an autofiltered list for which I need to rank
certain values every time I filter the table. I managed
to use the following formula to rank all values when the
table is not filtered:
Cells 1 to 2976 in Column B:
"F"&MATCH($A2,$A$2:$A$2976,0)+1&":F"&MATCH
($A2,$A$2:$A$2976,1)+1
Cells 1 to 2976 in Column C:
IF(ISERROR(RANK(F2,INDIRECT(B2)))=TRUE,"",RANK(F2,INDIRECT
(B2)))
Cells 1 to 2976 in Column F: Value
I used the match function because the list in column A
involves different groups
e.g. "Chocolate", "Vanilla", "Moka"..etc and I needed to
find the first and last row for every group and rank the
values accordingly.
When I filter the table I do it by product and region in
another column:
"Chocolate" in all USA or "Chocolate" for California only
Obviously the range changes when I filter the table and I
do not know how to solve this problem.
Any help will be greatly appreciated.
Thanks
Gustavo
I have an autofiltered list for which I need to rank
certain values every time I filter the table. I managed
to use the following formula to rank all values when the
table is not filtered:
Cells 1 to 2976 in Column B:
"F"&MATCH($A2,$A$2:$A$2976,0)+1&":F"&MATCH
($A2,$A$2:$A$2976,1)+1
Cells 1 to 2976 in Column C:
IF(ISERROR(RANK(F2,INDIRECT(B2)))=TRUE,"",RANK(F2,INDIRECT
(B2)))
Cells 1 to 2976 in Column F: Value
I used the match function because the list in column A
involves different groups
e.g. "Chocolate", "Vanilla", "Moka"..etc and I needed to
find the first and last row for every group and rank the
values accordingly.
When I filter the table I do it by product and region in
another column:
"Chocolate" in all USA or "Chocolate" for California only
Obviously the range changes when I filter the table and I
do not know how to solve this problem.
Any help will be greatly appreciated.
Thanks
Gustavo