Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting a sheet name reference within a cell reference??? | Excel Discussion (Misc queries) | |||
Changing sheet reference to cell reference | Excel Worksheet Functions | |||
Syntax for using variables in a cell formula to reference paths/fi | Excel Worksheet Functions | |||
Vary variables in a formula via reference to another cell | Excel Discussion (Misc queries) | |||
Trying to stack 2 variables on each other in one sheet | Excel Discussion (Misc queries) |