I'm Not sure what the function would be called so bear with me.......

[sh0t2bts]

I have one workbook that holds a manpower list, in this list is every person
who works for the company and whoes / what team they are in,


I then have 15 or so reports that I use to collect data on these teams, the
problem I have is if someone moves teams which they do each month, I have
all of the reports to update.

What I am after is keeping the list of people and which team there reside in
in my first manpower workbook and the been able to query that workbook for
the team members.thou only having to change one workbook )

Manpower workbook
A B
1 Fred T Team 1
2 John D Team 2
3 Mark W Team 2
4 Mary B Team 2
5 Paul L Team 1
6 Jo P Team 3
7 Marie B Team 3
8 Joanne P Team 1
9 Ian T Team 5

In another workbook or work sheet I would like to change the value in cell
A1 to the team name and then another range of cells pickup all of the people
in that team

A
1 Team 1
2 Fred T
3 Paul L
4 Joanne P

Any ideas how to achive this???

Cheers

Mark

 

[Jim]

Your explanation is a little too involved for the time I have to devote to
it now, but have you considered Data>Filter>Autofilter or Data>Pivot Table?

 

[sh0t2bts]

I have but what I have is 15 or so reports that collect data from an SQL
server when they are opened, They requere no mamual input as they work on
dates which I change using functions and the other part is names, I can not
do data>filter> Etc on the names

Mark

 

[Don Guillett]

Have a look in vbe HELP index for the FIND. The example shows using find
next.

 

[Ken Wright]

Mark, couple of Qs:-

Can two of your people have the same name - If so then how do you differentiate?
Is the number of teams defined or does it change - If so then by how many and
how often?
Is the total number of people defined or does it change often, If so, then
roughly how much does it vary month on month?

 

[Ken Wright]

OK, having just read your reply to Don, I am now confused about what are inputs
and what are outputs, and also what form they take.

Are you saying you collate 15 different SQL reports in a single workbook and on
a single sheet, or in a single workbook and on 15 different sheets - Also what
is on those reports and in what format. Either way, what is the required
output - Do you need 15 separate sheets, one with each team on it, or is one
sheet with all the data good enough, and are you just looking for a Team name
with a list of members?

 

[sh0t2bts]

Ken,

Sorry to be so confusing,

I need 15 different team lists on 15 diffrent sheets.

The number of people stays the same 168, Two people will not have the same
name if there where two sarah smiths one would be sarah smith2.
The number of teams also stays the same 15.


I wouldn't think about the SQL query as that is handled in another macro I
have all I need is a list of each team going from Cell X to Cell XX in one
column



Hope this is more clear

Mark

 

[Ken Wright]

Mark, I have a test file that I did for someone else that I have tweaked
slightly, that may give you what you want. It is set to space for 300 names
currently with dummy data in 165 cells, with 15 sheets, one for each team.
Updating one sheet will drive all the others You will only see data listed in
the team sheets where data exists, and hopefully no error messages anywhere.
File is 400K in size, or 52K zipped. I'm shooting off to bed shortly, so will
send you the zipped file anyway, but if you have problems with it then just let
me know if you want the unzipped file.

File is all based on arrays, and if per chance you are mailing out each of the
sheets, then you might want to consider using Ron de Bruins sendmail addin in
conjunction with this. Despite the arrays it runs quite quickly on my machine,
and I am only running a 900mhz AMD Athlon with XP and 512MB of Ram. Long way
off being a fast PC.

 

[Tushar Mehta]

It seems the data on each sheet is independent of the composition of
the team. If that is correct, and all you want is the current
composition of the team (extracted from the information on the first
worksheet), use a PivotTable with no data field. Set the team and name
as the row fields and lead the data field empty. That should give you
what you want.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions