G
Guest
Hello.
What I am trying to do is capture hours data from Hours_Data worksheet
(column F), based on a match of the User_ID and Line_Number field values,
whcih are listed on both worksheets.
The columns on the Hours_Data worksheet are as follows:
User_ID = Column A
Line_Number = Column E
Hours = Column F
The columns on Table1 worksheet are as follows:
User_ID = Column D
Line_Number = Column E
Hours = Column K
Here is a formula that I am using based on input form another user yesterday
(see below string of e-mails):
=INDEX(Hours_Data!$F$2:$F$6,MATCH(1,((Hours_Data!$A$2:$A$6=D2)*(Hours_Data!$E$2:$E$6=E2))))
(entered as an array formula)
The problem is that although I do receive values, they are out of sequence.
For example, as test data, if the hours values are 6, 7, 8, 9, and 10 in
column F (rows 2 through 6) of the Hours_Data worksheet, when the above
formula is entered and copied in cells K2, K3, K4, K5, and K6 on the Table1
worksheet, I receive the values 10, 10, 7, 8, 9!
What am I doing wrong. Does the column order matter on either worksheet? I
tried making Match_Type chnages (i.e. 1,0, and -1), I still didn;t receive
the correct values. Note that the values in the USER ID column are the same.
I am only doing a test as the data has lots of repeating USER IDs - each
representing a different form type - I don't know if this causes a problem.
Any suggestions would be appreciated.
Mark
--------------
What I am trying to do is capture hours data from Hours_Data worksheet
(column F), based on a match of the User_ID and Line_Number field values,
whcih are listed on both worksheets.
The columns on the Hours_Data worksheet are as follows:
User_ID = Column A
Line_Number = Column E
Hours = Column F
The columns on Table1 worksheet are as follows:
User_ID = Column D
Line_Number = Column E
Hours = Column K
Here is a formula that I am using based on input form another user yesterday
(see below string of e-mails):
=INDEX(Hours_Data!$F$2:$F$6,MATCH(1,((Hours_Data!$A$2:$A$6=D2)*(Hours_Data!$E$2:$E$6=E2))))
(entered as an array formula)
The problem is that although I do receive values, they are out of sequence.
For example, as test data, if the hours values are 6, 7, 8, 9, and 10 in
column F (rows 2 through 6) of the Hours_Data worksheet, when the above
formula is entered and copied in cells K2, K3, K4, K5, and K6 on the Table1
worksheet, I receive the values 10, 10, 7, 8, 9!
What am I doing wrong. Does the column order matter on either worksheet? I
tried making Match_Type chnages (i.e. 1,0, and -1), I still didn;t receive
the correct values. Note that the values in the USER ID column are the same.
I am only doing a test as the data has lots of repeating USER IDs - each
representing a different form type - I don't know if this causes a problem.
Any suggestions would be appreciated.
Mark
--------------