View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Arvi Laanemets Arvi Laanemets is offline
external usenet poster
 
Posts: 510
Default The Seemingly Impossible

Hi

I think the easiest way will be an ODBC query. Define your table as named
range
MyTable=MySheet!$A$1:$G$10000
(row 1 must contain headers), and save the workbook.

Now, on empty worksheet, create an ODBC query with SQL-string like this (on
fly)

SELECT TOP 5 MyTable.IncidentNumber, MyTable.Date,
MyDate.IncidentDescription, MyDate.Minutes1, MyDate.Minutes2 FROM MyTable
MyTable WHERE MyTable.IncidentNumber IS NOT NULL AND
MyTable.Function="FINANCE" AND MyTable.Area="CREDIT CONTROL" ORDER BY
MyTable.Minutes1 DESC

In query properties, set it to be refreshed on open (, and maybe after some
time interval too). Set data to be overwritten on refresh, and unused rows
to be cleared. Additionally, you can always refresh the query manually too.



--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Danny Lewis" wrote in message
...
Hello all

I posted yesterday, when Leo was very helpful but I'd like to try and post
it again and see if there are any geniuses out there...

I have a table, with the following columns:

Incident Number, Date, Incident Description, Minutes 1, Minutes 2,
Function,
Area (and a few other irrelevant columns).

This table has a few thousand records in it, with each column varying
data.
There are about 6 different functions, and 30 different areas.

Say for example one function was FINANCE, and the area was CREDIT CONTROL,
is there a way I could produce the same table on another spreadsheet for
the
top 5 incidents for these two variables, descending by Minutes 1?

e.g. on another worksheet

Incident Number, Date, Incident Description, Minutes 1, Minutes 2,
Function,
Area
1 01/01/01 ikgjnsgosgn 78 120 Finance Credit
Control
2 23/09/00 jsdgofnyof 65 103 Finance Credit
Control

and so on????

Thanks

Danny