Will a Pivot Table Help with my problem?

[Guest]

I am putting together a form that will be used throughout our organization.
There are specific jobs linked to specific departments. How can a display a
list of only those jobs associated with the department number that is keyed
in? This is a big organization and managers are not going to want to scroll
through every job we have just to find the ones in their department. Help is
appreciated.

 

[Debra Dalgleish]

A pivot table is good for summarizing numerical data, so it may not be
what you need.

You could use a filtered list to display the relevant data. There are
instructions for AutoFilters in Excel's Help, and here:

http://www.contextures.com/xlautofilter01.html

 

[Guest]

This helped. thanks! Now I have afollow up: Is there any way the
Department # in the filter can be linked to the department # that i entered
in the form. What I would like to do is just have my managers key in their
department # (and not have to scroll through all the department #s in the
filter) and then the jobs in their department will show up. Is this possible?

 

[Debra Dalgleish]

You could do that with an advanced filter and a bit of programming. I
have a sample workbook here:

http://www.contextures.com/excelfiles.html

Under Filters, look for "Filter Rows for Text String"

 

[Guest]

Thanks for this help. I went through the workbook samples and found one that
fit my issue. My problem is that when I set up the advanced filter, it would
pull in the data for the first dept # selected, but when I changed it, the
new data didn't show up. I am sure it is an issue with the programming, but
I have no idea how to troubleshoot this. More help would be appreciated.
Thanks!

 

[Debra Dalgleish]

Which example did you use?

Are you typing the department numbers, or trying to loop through a list
of numbers?

 

[Guest]

I used the one below:

Phone List for Selected Name -- uses Data Validation and an Advanced Filter
to extract a list of customers with the selected Last Name; file contains a
macro which automates the filter (XL2000 and XL2002 only) PhoneList.xls 36 kb

I set up a drop down list that had the department #s. That part worked. I
just could never get the corresponding job information for the department to
pop up.

 

[Debra Dalgleish]

Right-click on the Data Entry sheet tab, and select View Code.

You may have to edit the code, to match the layout of your worksheets.