Hi Scott,

INDEX, as help says, returns the value of an element in a table or an array,
selected by the row and column number indexes. So it is similar to the
LOOKUP functions but finds the value based upon a row and column index
rather than a value. Because of this, it is often combined with MATCH which
does lookup a value, but returns the row number.

So in the example I gave

MATCH(A1,Sheet1!A:A,0)

finds the row within the column of our value and returns the row number, and
then

INDEX(Sheet1!B:B,MATCH(A1,Sheet1!A:A,0))

returns the value in column B for our row. In the example I gave, VLOOKUP
would also have worked

=VLOOKUP(A1,Sheet1!A1:B11,2,FALSE)

but INDEX & MATCH works better when the data is not organised with the key
value to the left. In the example I gave I felt that the data was more
likely to be something like

Client Id || Client Name || some other data || value || etc

so the lookup is on value, INDEX and MATCH can handle this.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Scott B. Hogle" wrote in message
...
Thanks to everyone!

I used Bob's suggestion and it worked great. I never
would have figured that out on my own.

I still don't know how the INDEX Function works, but I
was able to use Bob's coding and now the client name and
sales results are tied together.

Thanks again, you guys are awesome!


SBH
-----Original Message-----
Scott,

You can do it without sorting.

On the summary page, for the Top 10, you can get the
value like so

=LARGE(Sheet1!A:A,ROW(A1))

and just copy down 9 rows.

If you want some related data, use a formula like

INDEX(Sheet1!B:B,MATCH(A1,Sheet1!A:A,0))


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Scott B. Hogle"
wrote in message
...
Is there a way that Excel can automatically sort a
column?

I am working on a spreadsheet where the end user "cut
and
pastes" data from an outside source to Excel. The
Excel
spreadsheet then manipulates the data and creates a one
page presentation piece.

One of the features on the presentation piece is a "Top
10 Client" listing - this is why I want automatic
sorting.

I know how to sort the data manually, using the Data -
Sort technique, but I was hoping I wouldn't have to
train
the end users how to perform that step.

Any ideas?

Thanks in advance for your help.


Scott B. Hogle


.