Listing Employees by Area Code

[carsey]

Hi all,

I am in need to some help with Excel 2007 if possible

I need to be able to create a method of showing which employees are based in which area code.

An example, to make it a bit clearer is to list, the employees Unique Number, their name, age and length of service in a table that ranges from C6:F17 with the required information when a combo box at the top of the table can be manually changed between the area codes available.

I have thought of using a VLookup to get the required information, but I couldnt figure out how to make it list the examples of all the employees. Could it be VBA code?

Chris

[martindwilson]

post an example

[JBeaucaire]

The simplest technique is to turn on the Data > Filter > Autofilter on this data set and then filter that area code column by the area code of interest rather than a combobox. It has the benefit of being simple.

[carsey]

Thanks for the messages.

I really need it to filter the data using the combo box at the top as seen in the screenshot.

An example is below:

\1

The employee details are all stored in a separate sheet.

[martindwilson]

so you are trying for 2 criteria manager and area? really need to see the actual layout in a workbook. or just do a mock up with fictitious data

[carsey]

Cheers.

I uploaded the workbook for you to have a look. Its the form in the AreaStaff sheet and the data needs to be pulled and filtered from the StaffData sheet.

Basically, I need a method of listing the employee numbers based on the single area code selected in C3. Then I can do a Vlookup on the employees unique number to get the rest of the data.

Cheers

[martindwilson]

here's how i'd approach it i extended the named range lookup employees to include column B
fixed your vlook ups to include "false" option so as to find an exact match
and inserted a helper column to look up from on sheet staff data. i would probably done it slightly different from scratch but i just worked with what you have already done.
i couldn't do grade/ salary/bonus rate columns as i don't know where that data is!

[carsey]

Cheers for that!. Works like a charm.

Now, i have created a IF and AND function to work out which grade the employee needs to be assigned.

This is the criteria

G1: Under 21 and less than 4 years service
G2: 21 or more and less than 4 years service
G3: 21 or more with more than 4 years service
G4: 52 or more with 24 or more years service

The formula I current have is:

=IF(AND((E6<21),(F6<4)),"G1",IF(AND((E6>=21),(F6<4)),"G2",IF(AND((E6>=21),(F6>=4),(F6<=24)),"G3",IF(AND((E6>=52),(F6>=24)),"G4",FALSE))

Which works, but not fully.
The problem is, that if someone is under 52 years old and has more than 24 years service, the cell returns FALSE.

What do I need to add into my formula to make it look for the right values.

Chris

[martindwilson]

what should it = then if under 52 years old and has more than 24 years service?

[carsey]

It should equal G3

[martindwilson]

=if(and((e6<21),(f6<4)),"g1",if(and((e6>=21),(f6<4)),"g2",if(and((e6>=52),(f6>=24)),"g4",if(and((e6>=21),(f6>=4)),"g3",false))))

[carsey]

Cheers buddy! :D

[davesexcel]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save