Hi



"Minerva" wrote in message
...
Thanks for the help, but the items under each of the 3 columns are
duplicated
for both sheets...several entries of an item exist for each....how to
retrieve multiple values for a particular combination of the 3 column
items?
I tried using getpivotdata() but it doesn't retrieve multiple values.
Any other idea?
Thanks

But I just explained how to get 'multiple values for a particular
combination of the 3 column items'!

Here is a copy from my almost a year-old answer to some similar question (it
was for single condition only). Try to create a workbook following those
instructions - maybe it helps you to catch the logic. On sheet
EmployeesByLocation is created a list of all employees in certain location
from table Employees.

****
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). You 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