select data from a table

[UKMAN]

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.

 

[Peo Sjoblom]

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_function_page.htm#Home


it's number 6

However the best way is IMHO definitely filtering



--


Regards,


Peo Sjoblom

 

[UKMAN]

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

 

[UKMAN]

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

 

[Peo Sjoblom]

If you can make up a small example of your table replacing any sensitive
data with nonsense data
in one sheet and manually put the result you expect in another sheet and
email the example workbook to me I can have a look at it tonight and email
it back to you with a formula solution (if possible)

my email is (after removing all caps and replace at with @)

terre08NOSPAMatKILSPAMMERSgmail.com


--


Regards,


Peo Sjoblom

 

[UKMAN]

Peo,

I have sent an example

many thanks for all your help.

Colin

 

[Peo Sjoblom]

I have not received it yet, you are sure you sent it to

terre08

@


gmail.

com


?


note that I won't have time to look at it until tonight my time (US Pacific)
so if you are in UK you won't have it until tomorrow


--


Regards,


Peo Sjoblom

 

[UKMAN]

Peo,

Thick as I am I misunderstood your email clue.
have resent, sorry.
Colin

 

[UKMAN]

Peo,

I got a blank reply?? did you get my email OK?

Cheers
Colin

 

[Peo Sjoblom]

I got your email, I also responded inline for some clarifications so it
shouldn't be a blank reply. Scroll down a bit


--


Regards,


Peo Sjoblom

 

[UKMAN]

peo,

I have replied and sorry for being ....

Colin