I have an Excel 2007 workbook that I use for staff capacity planning.  I budget for 8 different groups within a cost center, and have built 8 different spreadsheets.  Each of the 8 groups has a supervisor or manager who is responsible for that group, and they are tasked with populating supply and demand.  

I have a seperate sheet in this workbook that lists the names and titles of all staff, as well as the group to which they belong.  What I would like to do is display a table on each worksheet that lists the staff corresponding to that group.

For example, if there are 3 out of 10 individuals on the Staff worksheet that have a group name that matches "Group A," then I would like to display a list containing those three names on the Group A tab.

Thinking with my Access background, I visual a table that is populated with "SELECT Staff, Title from Staff WHERE Team Like 'Group A'".  How can I replicate something like that in Excel without manually editing/updating each individual worksheet?

hi,

Use MS Query as a parameter query, where the parameter would be a cell on the sheet that contains the group name.

FAQ68-5829: Using MS Query to get data from Excel

 

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

Hi Skip,

Thank you for the link, I was not familiar with MS Query.  I am, however, experiencing an error.  I have inserted a table with the appropriate data (it ended up just using the existing data on the sheet).  I have highlighted all of my data, clicked Control+F3 and defined a name.  I chose "Database" after encountering the error I'm about to mention, but that did not resolve the issue.  I saved the workbook, then attempted to add a query.  I receive the following error:

"This data source contains no visible tables."

I checked the Staff sheet again:  there is no data outside of the table and there are no blank cells inside the table.

Suggestions?

 

Figured it out:  I had to enable "System Tables" in the options dialog of MS Query.  Weird, but hey.

Thanks!

Quote:

This data source contains no visible tables.

There is a block of BOLD RED TEXT

If you do not see your SHEET NAMES in the Add Tables Window, click the OPTIONS Button and check all boxes

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

<SIGH>  Only two cups of coffee so far this morning.  My reading comprehension skills are currently those of a 6 month old.  That as a very well-deserved reminder, thanks!