I have a sheet1 where users will enter an unknown number of items in column
A, and a rating from 1-10 /or/ the letter "M" in column B.

On sheet2, I want to (re)show those items and their ratings in the following
order, based on their rating: all 'M' first, then in descending order, and
have this list automatically update/recalculate when any item is added or
changed on the Sheet1.

Item X M
Item C M
Item K M
Item J 10
Item W 10
Item A 9
Item F 8
Item Y 8
Item N 8
etc.

I know how to do this in VBA, but I'm trying to complete this workbook
without any macros because some users have their macro security set on high
by default and I'd like this to work for everyone without asking them to
change their security settings.

I'm sure there is a way to do this with just formulas (lookups, match,
etc.), but I'm struggling trying to find a combination that will work, since
each row shouldn't repeat data that is already showing above, and should
find the next highest item to bring over.

Helpful hints please?
Thanks,
Keith

Assuming that A2:B10 contains the data, try the following formula, which
needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

D2, copied down and over to Column E:

=IF(ROWS(D$2:D2)<=COUNTA($B$2:$B$10),INDEX(A$2:A$1 0,MATCH(LARGE(IF($B$2:$
B$10="M",100,$B$2:$B$10)-ROW($B$2:$B$10)/10^5,ROWS(D$2:D2)),IF($B$2:$B$10
="M",100,$B$2:$B$10)-ROW($B$2:$B$10)/10^5,0)),"")

Since the range for your data will be changing, you can either...

1. convert the data into a list (Data List Create List), if you have
Excel 2003 or later, or

2. use dynamic named ranges

Hope this helps!

In article ,
"KR" wrote:

I have a sheet1 where users will enter an unknown number of items in column
A, and a rating from 1-10 /or/ the letter "M" in column B.

On sheet2, I want to (re)show those items and their ratings in the following
order, based on their rating: all 'M' first, then in descending order, and
have this list automatically update/recalculate when any item is added or
changed on the Sheet1.

Item X M
Item C M
Item K M
Item J 10
Item W 10
Item A 9
Item F 8
Item Y 8
Item N 8
etc.

I know how to do this in VBA, but I'm trying to complete this workbook
without any macros because some users have their macro security set on high
by default and I'd like this to work for everyone without asking them to
change their security settings.

I'm sure there is a way to do this with just formulas (lookups, match,
etc.), but I'm struggling trying to find a combination that will work, since
each row shouldn't repeat data that is already showing above, and should
find the next highest item to bring over.

Helpful hints please?
Thanks,
Keith

Wow, I never would have figured that out- I'm not sure I understand it even
now! I'll give it a try, and also tease it apart to try to understand how it
works.
Many thanks!!
Keith

"Domenic" wrote in message
...
Assuming that A2:B10 contains the data, try the following formula, which
needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

D2, copied down and over to Column E:

=IF(ROWS(D$2:D2)<=COUNTA($B$2:$B$10),INDEX(A$2:A$1 0,MATCH(LARGE(IF($B$2:$
B$10="M",100,$B$2:$B$10)-ROW($B$2:$B$10)/10^5,ROWS(D$2:D2)),IF($B$2:$B$10
="M",100,$B$2:$B$10)-ROW($B$2:$B$10)/10^5,0)),"")

Since the range for your data will be changing, you can either...

1. convert the data into a list (Data List Create List), if you have
Excel 2003 or later, or

2. use dynamic named ranges

Hope this helps!

In article ,
"KR" wrote:

I have a sheet1 where users will enter an unknown number of items in
column
A, and a rating from 1-10 /or/ the letter "M" in column B.

On sheet2, I want to (re)show those items and their ratings in the
following
order, based on their rating: all 'M' first, then in descending order,
and
have this list automatically update/recalculate when any item is added
or
changed on the Sheet1.

Item X M
Item C M
Item K M
Item J 10
Item W 10
Item A 9
Item F 8
Item Y 8
Item N 8
etc.

I know how to do this in VBA, but I'm trying to complete this workbook
without any macros because some users have their macro security set on
high
by default and I'd like this to work for everyone without asking them to
change their security settings.

I'm sure there is a way to do this with just formulas (lookups, match,
etc.), but I'm struggling trying to find a combination that will work,
since
each row shouldn't repeat data that is already showing above, and should
find the next highest item to bring over.

Helpful hints please?
Thanks,
Keith