I know using Acess would be easier, though I havent tackled it yet, I want to
be able to create a seperate sheet that has everyone of our employees who
work in say "Staten Island", So the main Database page is where the info is
stored, but kept there and moved to a seperate Staten Island Page... It
sounds like an Acess function but I was wondering if it can be done using
Excel... Thanks.

Why use a separate sheet?....just use Data Filter Autofilter to view only
the Staten Island or any other selected groups in your main
database.......then Data Filter Autofilter (it's a toggle) to return to
normal.....

Vaya con Dios,
Chuck, CABGx3




"Sr. Vice Deli" wrote:

I know using Acess would be easier, though I havent tackled it yet, I want to
be able to create a seperate sheet that has everyone of our employees who
work in say "Staten Island", So the main Database page is where the info is
stored, but kept there and moved to a seperate Staten Island Page... It
sounds like an Acess function but I was wondering if it can be done using
Excel... Thanks.

Hi

Let's assume you have a sheet Employees, with an employee list on it. Let
the table structure to be:
XXX, EmployeeCode, FirstName, LastName, Location, ...
At least one column in table must be a key column - and always filled when
there are any data in row. I assume it is EmployeeCode (column B).
About column XXX we'll speak later.

It'll be clever to have a sheet Locations, where all locations are listed.
It can be a single-column list Location, or a table containing additional
info with coluimn Location as leftmost. Define the column Location as
dynamic named range Locations
Locations=OFFSET(Locations!$A$1,1,,COUNTIF(Locatio ns!$A:$A,"<")-1,1)
(I assume the 1st row is header row).

Now on Employees sheet, you can format the Location column as data
validation list with Source=Locations.

Add 3rd sheet - p.e. EmployeesByLocations. Into cell A1 enter "Location:".
Format the cell B1 using data validation list with Source=Locations. Define
the cell B1 as named range p.e. Location
Location=EmployeesByLocations!$B$1

On sheet Employees, into 2nd row of column XXX (A2) enter the formula
=IF(B2=Location,COUNTIF(B$2:B2,Location),"")
and copy it down at least for all rows with employees (but you can have
spare rows at bottom). ou get numbered all rows with same location as
selected on 3rd sheet.

Create a dynamic named range EmployeeTbl
EmployeeTbl=OFFSET(Employees!$A$1,1,,COUNTIF(Emplo yees!$B:$B,"<")-1,#)
where # is the number of columns in Employees table. You can hide the column
XXX now.

On sheet EmployeesByLocations, into row 3 enter headers:
EmployeeCode, FirstName, LastName, ...
NB! You don't need the column Location here anymore!

Into A4 enter the formula
=IF(ISERROR(VLOOKUP(ROW()-3,EmployeeTbl,2,0)),"",VLOOKUP(ROW()-3,EmployeeTbl
,2,0))
Into B4 enter the formula
=IF(A4="","",VLOOKUP(ROW()-3,EmployeeTbl,3,0))
Into C4 enter the formula
=IF(A4="","",VLOOKUP(ROW()-3,EmployeeTbl,4,0))
Into D4 enter the formula (when there were columns after Location in
Employees table)
=IF(A4="","",VLOOKUP(ROW()-3,EmployeeTbl,6,0))
etc.

Select cells with formulas in row 4, and copy formulas dows for as much rows
as you think you need. It's all. Select any location, and you get the list
of employees in this location.


Arvi Laanemets


"Sr. Vice Deli" <Sr. Vice wrote in message
...
I know using Acess would be easier, though I havent tackled it yet, I want
to
be able to create a seperate sheet that has everyone of our employees who
work in say "Staten Island", So the main Database page is where the info
is
stored, but kept there and moved to a seperate Staten Island Page... It
sounds like an Acess function but I was wondering if it can be done using
Excel... Thanks.