Peo,

I have had another go with adv filter with more luck but the problem is with
the need for the data to be dynamic as mentioned i.e. on change of dept name
or new record is inserted the list is refreshed to reflect the change with no
user intervention. Adv filter will not do this.

I have played again with your example and got it to produce the first match
but when I copy it down it still just brings back the same record. Below is
my formula for bring back the name of the individual only

{=IF(ROWS($1:1)<=COUNTIF(Bud_level,$L$9),INDEX(Bud _name,SMALL(IF(Bud_level=$L$9,
ROW(Bud_level)-ROW($L$9)+1),ROWS($1:1))),"")}

bud_level is the data area (not including headings)
bud_name is the list of individuals names
$L$9 is the cell reference of the question.

Hope this helps and cheers

Colin
"UKMAN" wrote:

Peo,

many thanks for the help but the report need to be continually updated
either by change to the question i.e. Dept=admin to Dept=IT, or as a new row
is created.

The advanced filter doesn't seem to work correctly probably me :) and it
will not copy to another sheet??

I have had a go with your formula but just getting a blank field returned??

many thanks and I'll just have to keep trying. Just wish they would let me
do a course.

Colin
"Peo Sjoblom" wrote:

This what filters are made for, you could set up an advanced filter and use
"Dept" and "Admin" as
a 2 cell criteria range than you can select copy to another location in the
filter setup.
If you want formulas you would need 5 versions (where one would change the
INDEX part in each of them) of the same rather complicated array formula,
then those 5 formulas need to be copied down as far as needed. An example of
such a formula can be found here (you can also download a sample workbook)

http://nwexcelsolutions.com/advanced..._page.htm#Home


it's number 6

However the best way is IMHO definitely filtering



--


Regards,


Peo Sjoblom


"UKMAN" wrote in message
...
i have a table with about 200 rows and need to match a single keyword
question to a field in a row then copy that rows data to another summary
table.

e.g.
Question: Dept = Admin

Table: (The data is in an array name of "budlevel")
Dept, Team, Name, Course, Cost, Status
Admin A1 Sid x 1 Booked
IT IT1 Bill y 1 Res
Catering C1 Mike x 1 Booked
Admin IT2 Colin x 1 Booked
Admin IT1 Jane x 1 Res

Summary:
Team, Name, Course, Cost, Status
A1 Sid x 1 Booked
IT2 Colin x 1 Booked
IT1 Jane x 1 Res

I am using the following formula =VLOOKUP(Question,Bud_level,2,0) which
will
give me the name in one line but I cannot figure out how to make it search
all the rows and bring back the example summary

I have had help that says use advanced filter but I need to keep changeing
the "question" field and then print out which this doesn't allow me to do.

Cheers
In advance for any and all advice.