returning a cell's value and a corresponding value

I'm attaching the exel file I'm having problems with.

I'm trying to write a forumula that will list the top 5 "TotalCharges"
in the "reservations" worksheet, and the corrisponding "CustNo". You
can see what I attempted on the "totalcharges" worksheet.

I can return the actual "TotalCharges" values, but not the
corresponding "CustNo" that goes along with it. Is there a way to do
this so it works?

Or, can this not be done with formulas? Should it somehow be done in a
pivot table? I'm stumped.

Thanks in advance


+-------------------------------------------------------------------+
|Filename: reservation data.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4063 |
+-------------------------------------------------------------------+

--
Force Flow
------------------------------------------------------------------------
Force Flow's Profile: http://www.excelforum.com/member.php...o&userid=29054
View this thread: http://www.excelforum.com/showthread...hreadid=487855

Hi!

On the Reservations sheet:

Use another column, column L. Give it the header Rank.

In L2 enter this formula and copy down to L46:

=RANK(K2,$K$2:$K$46)+COUNTIF($K$2:K2,K2)-1

In the Topcharges sheet:

Enter this formula in C2 and copy down to C6:

=INDEX(reservations!C$2:C$46,MATCH(A2,reservations !L$2:L$46,0))

The top 5 customers are all C0005.

Biff

"Force Flow" wrote
in message ...

I'm attaching the exel file I'm having problems with.

I'm trying to write a forumula that will list the top 5 "TotalCharges"
in the "reservations" worksheet, and the corrisponding "CustNo". You
can see what I attempted on the "totalcharges" worksheet.

I can return the actual "TotalCharges" values, but not the
corresponding "CustNo" that goes along with it. Is there a way to do
this so it works?

Or, can this not be done with formulas? Should it somehow be done in a
pivot table? I'm stumped.

Thanks in advance


+-------------------------------------------------------------------+
|Filename: reservation data.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4063 |
+-------------------------------------------------------------------+

--
Force Flow
------------------------------------------------------------------------
Force Flow's Profile:
http://www.excelforum.com/member.php...o&userid=29054
View this thread: http://www.excelforum.com/showthread...hreadid=487855

Biff Wrote:
Hi!

On the Reservations sheet:

Use another column, column L. Give it the header Rank.

In L2 enter this formula and copy down to L46:

=RANK(K2,$K$2:$K$46)+COUNTIF($K$2:K2,K2)-1

In the Topcharges sheet:

Enter this formula in C2 and copy down to C6:

=INDEX(reservations!C$2:C$46,MATCH(A2,reservations !L$2:L$46,0))

The top 5 customers are all C0005.

Biff

"Force Flow"
wrote
in message
...

I'm attaching the exel file I'm having problems with.

I'm trying to write a forumula that will list the top 5
"TotalCharges"
in the "reservations" worksheet, and the corrisponding "CustNo".
You
can see what I attempted on the "totalcharges" worksheet.

I can return the actual "TotalCharges" values, but not the
corresponding "CustNo" that goes along with it. Is there a way to
do
this so it works?

Or, can this not be done with formulas? Should it somehow be done in
a
pivot table? I'm stumped.

Thanks in advance



+-------------------------------------------------------------------+
|Filename: reservation data.zip
|
|Download: http://www.excelforum.com/attachment.php?postid=4063
|

+-------------------------------------------------------------------+

--
Force Flow

------------------------------------------------------------------------
Force Flow's Profile:
http://www.excelforum.com/member.php...o&userid=29054
View this thread:
http://www.excelforum.com/showthread...hreadid=487855


Great! Thanks! That did the trick


--
Force Flow
------------------------------------------------------------------------
Force Flow's Profile: http://www.excelforum.com/member.php...o&userid=29054
View this thread: http://www.excelforum.com/showthread...hreadid=487855