Thank you very much for your help, unfortunately I described what I was
trying to do very poorly and I can't seem to get it to work, I issued another
message board question. Your suggestion would have definitely worked for the
situation I described!
"Max" wrote:
One play which might work for you ..
Sample construct at:
http://www.savefile.com/files/2840182
Limiting_the_range_of_a_lookup_function_Dorn_wks.x ls
Assume source data is in Sheet1 cols A & B, from row2 down
Using 3 empty cols to the right of the data
Put in C2: =IF(OR(A2="",ISNUMBER(A2)),"","x")
Put in D2: =COUNTIF($C$2:C2,"x")
Put in E2: =INDEX(A:A,MATCH(D2,D:D,0))
Select C2:D2, copy down till last row of data
In Sheet2,
employee names are listed in B2 across
dates are listed in A2 down
-------------- Emp1 Emp2 Emp3
01-Nov-05
02-Nov-05
03-Nov-05
etc
Put in B2, and array-enter (press CTRL+SHIFT+ENTER):
=IF(ISNA(MATCH($A2&"_"&B$1,Sheet1!$A$2:$A$100&"_"& Sheet1!$E$2:$E$100,0)),"",
INDEX(Sheet1!$B$2:$B$100,MATCH($A2&"_"&B$1,Sheet1! $A$2:$A$100&"_"&Sheet1!$E$
2:$E$100,0)))
Copy B2 across and fill down to populate the table
Adapt the ranges to suit ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Dorn" wrote in message
...
I have data exported into excel from another program. The way the data is
presented is as follows:
Employee Name
Date data
Date data
Date data
Date data
Date data
Date data
Another Employee Name
Date data
Date data
Date data
Date data
and on and on,
What I'm trying to do is make a new tab that lists all of the employee
names
in a row and dates down a column and then use a look up function (I was
trying index, match) to compare the dates from both tabs enter the data
for
each employee. The problem I'm facing is that under each employees name
their are a different number of rows (some people didn't work some days),
so
I want to make the range for the look up to compare dates going down the
spreadsheet only until it hits a new employees name (or hits a row with
the
word "agent" in it) then I want it to continue comparing the dates and
entering the data, but I want the next employees data to show up under
their
name on the new spreadsheet. How do I make it so I don't have to choose a
set range? But make it so it looks between two values? Aaaagh sooo
confused.