Filters (Excel 2007)

[steveh]

Morning all

What I am looking to achieve is to have 2 lists on one page, the first for Months and the second for Employees, For the months my initial selection would be January and for the Employees a message to say use the filter to select the Employee that you want. It seems, unless I am missing something that Excel allows only one filter on a page because if I filter months the filter button disappears from Employees and vice versa, is that correct?

[Rudi]

You are correct.
You can only have one list at any given point of time containing autofilters.

- Are you needing related filters that are dependent on the previous filters? You might consider dependent validation lists
- You could test out pivot tables if that might group and summarize the entries the way you want?
- The only other option is to use macros to hide rows that meet your conditions between the two or more lists.

[steveh]

You are correct.
You can only have one list at any given point of time containing autofilters.

- Are you needing related filters that are dependent on the previous filters? You might consider dependent validation lists
- You could test out pivot tables if that might group and summarize the entries the way you want?

Hi Rudi

thanks for the quick response. They are not dependant upon each other but thanks for the link, I have used Debora's advice many times.

I was just trying to be smart (that sums it all up, it was doomed to failure combining me and smart in one sentence!!) by making an index sheet that did not have a huge initial list of links (all of the months and all of the 100 Employee's are hyperlinked to their respective pages).

I can live with how it is in the screenshot but it would have been nice to have condensed it because then there departmental report pages etc. that I could have used the same technique on so that when the user opened the page they would be faced with just a few options

Cheers

[Rudi]

I'm not sure if you saw it, but I added a third option to the initial list I posted...using macros to hide entries that do not meet conditions that are specified. Macros can also then hide sheets that are not needed based on what the user wants to see. I can envision a navigational index that can hide (or navigate to) the sheets based on what the user clicks in the index page.

Can you provide more detail on how you think it can best work to limit the amount of sheets and index entries...

Addendum:
Here is a brief idea of navigation by hyperlinks... (Maybe this can be of use too?)

Navigation.xlsx

[steveh]

I'm not sure if you saw it, but I added a third option to the initial list I posted...using macros to hide entries that do not meet conditions that are specified. Macros can also then hide sheets that are not needed based on what the user wants to see. I can envision a navigational index that can hide (or navigate to) the sheets based on what the user clicks in the index page.

Can you provide more detail on how you think it can best work to limit the amount of sheets and index entries...

Addendum:
Here is a brief idea of navigation by hyperlinks... (Maybe this can be of use too?)

Navigation.xlsx

Hi Rudi

Thanks for this but in my initial screen shot they are all actually hyperlinks so that part is not a problem, what I was trying to do was make it more manageable for staff to see initially only a small amount of options so if they clicked on the Employee selection in my first post they would see this screenshot which shows all of the employees (the names will change dynamically from Emp1 to Fred Bloggs (for example) when the name is added to their profile) and they could scroll down and select the Employee they wanted to see detailed information on.

I am using a Pivot table for overall department summaries which lump the absence types by month but these views will show individual days taken so that we can be aware of the 'Friday, Monday and Public Holiday' abusers.

[HansV]

An alternative would be to create a userform in the Visual Basic Editor with a list box (or combo box) from which the user can select an employee, and another list box (or combo box) from which the user can select a month, plus a command button that activates the relevant worksheet for that employee and month.

[steveh]

An alternative would be to create a userform in the Visual Basic Editor with a list box (or combo box) from which the user can select an employee, and another list box (or combo box) from which the user can select a month, plus a command button that activates the relevant worksheet for that employee and month.

Hi Hans

Thanks, I had considered this along with a coded combo box that allowed hyperlinks but so far the workbook has no code and I was trying to keep it codeless but this is probably the best way to go

Cheers