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.

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.

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.

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.

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" wrote in message
...
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.

Peo,

I have sent an example

many thanks for all your help.

Colin

"Peo Sjoblom" wrote:

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" wrote in message
...
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.

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" wrote in message
...
Peo,

I have sent an example

many thanks for all your help.

Colin

"Peo Sjoblom" wrote:

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" wrote in message
...
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.

Peo,

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

"Peo Sjoblom" wrote:

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" wrote in message
...
Peo,

I have sent an example

many thanks for all your help.

Colin

"Peo Sjoblom" wrote:

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" wrote in message
...
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.

Peo,

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

Cheers
Colin

"UKMAN" wrote:

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.

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" wrote in message
...
Peo,

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

Cheers
Colin

"UKMAN" wrote:

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,

I have replied and sorry for being ....

Colin :(

"Peo Sjoblom" wrote:

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" wrote in message
...
Peo,

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

Cheers
Colin

"UKMAN" wrote:

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.