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.