Lookup and display unknown number of values 1

[PRMiller2]

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?

 

[SkipVought]

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



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PRMiller2]

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?

 

[PRMiller2]

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

Thanks!

 

[SkipVought]

This data source contains no visible tables.

There is a block of [red]BOLD RED TEXT

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

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PRMiller2]

<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!