Need to reference another sheet for two variables
 

Here are my sheet 1 column headings:
Client #, Client Name, Location, Employee, Date

On Sheet 2, I want to show every instance where an employee did billable
work on a certain date, and show which client they were working on.

Ex: If Kevin (column d) did billable work on date x (column e), who was it
for (column b)?

Sheet 1 is constantly updated and I want sheet 2 to reflect all changes.

Thanks in advance to anyone who can assist with this issue.

Need to reference another sheet for two variables
 

Sheet1 named ranges:

Client Name = Client
Employee = Emp
Date = Date

Sheet2:

A2 = some employee name
B2 = some date

Enter this formula in C2:

=SUMPRODUCT(--(Emp=A2),--(Date=B2))

That will return the count of records that meet the criteria.

Enter this array formula** in A3:

=IF(ROWS(A$3:A3)<=C$2,INDEX(Client,SMALL(IF((Emp=A $2)*(Date=B$2),ROW(Client)-MIN(ROW(Client))+1),ROWS(A$3:A3))),"")

Copy down until you gets blanks. You need to copy the formula to a number of
cells that is greater than or equal to the max number of records that will
meet the criteria.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"I M Desperate!!!" wrote in message
...
Here are my sheet 1 column headings:
Client #, Client Name, Location, Employee, Date

On Sheet 2, I want to show every instance where an employee did billable
work on a certain date, and show which client they were working on.

Ex: If Kevin (column d) did billable work on date x (column e), who was
it
for (column b)?

Sheet 1 is constantly updated and I want sheet 2 to reflect all changes.

Thanks in advance to anyone who can assist with this issue.